none
Dynamic Slicer RRS feed

  • Question

  • Hi,

    My base data is in the below format :

    Payer Region            Receiver Region            Amount Paid / Received

    Asia                         EMEA                           30

    Asia                         NAM                             20

    Asia                         Asia                             50

    EMEA                      NAM                             10

    EMEA                      Asia                              30

    EMEA                      EMEA                            60

    There are two transactions of 100 each. The desired output is the calculation of Net amount by region.

    In the above example, i need the following output :

    Asia : -100 +50 + 30 = -20

    EMEA : -100 + 30 + 60 = -10

    NAM : 30 + 10 = 30

    Hence, this is net Zero at total level, just allocation within regions. This is just two examples, my base data goes into granular level wherein i've details by country and by client which i need to show in the output.

    Kindly help!!

    Thank you!

    Monday, October 7, 2019 3:32 PM

Answers

All replies

  • Excel 365 Pro Plus with PowerPivot and Power Query.
    Granular level will be far more difficult.
    http://www.mediafire.com/file/amnd4nfek6oywcb/10_07_19.xlsx/file
    http://www.mediafire.com/file/komlaznpevxuv8n/10_07_19.pdf/file

    Monday, October 7, 2019 7:57 PM
  • thank you! Can we alter the base data in a way that will help us in Power BI? I need build an interactive dashboard analyzing attribution by geography and by client.

    Thank you!

    Regards,

    Raj.

    Tuesday, October 8, 2019 7:55 AM
  • Hi Raj. Power Query in Excel is the same technology underlying the "Get Data" features in PBIDesktop, and PowerPivot is the basis of the Modeling features in PBIDesktop. So you should be able to migrate the solution Herbert created over to Power BI pretty easily.

    In fact, there's a feature in PBIDesktop designed to help you do just that.

    Ehren

    Wednesday, October 9, 2019 10:08 PM
    Owner
  • Thank you! i'll check the links shared by Herbert. Ideally i want to expand this at a granular level. Will reach out to you guys incase i'm stuck. Thanks a lot for the help and support!
    Thursday, October 10, 2019 11:56 AM
  • Hi, i'm sorry i'm not able to get the desired output with the steps shown. Is there a way to kindly elaborate further?<o:p></o:p>

    What i did till now :<o:p></o:p>

    1. Grouped the base data in another table with Payer Geography and Amount<o:p></o:p>

    2. Grouped the base data in another table with Receiver Geography and Amount<o:p></o:p>

    3. Trying to append these tables/queries into a new table as shown in the snapshot but there are a lot of null values and the output is into 4 columns, two each from payer and receiver<o:p></o:p>

    4. I've one more query which has the Geography which will be the base to then advance group the Paid and Received tables. However, need help to proceed with this as well.<o:p></o:p>

    Kindly help!<o:p></o:p>

    Thank you.<o:p></o:p>

    Friday, October 11, 2019 12:19 PM
  • Share file or read this book:
    "Power Query for Power BI and Excel" by Chris Webb.

    Friday, October 11, 2019 5:51 PM