locked
many2many Relationship !? Aggregation, Pivot and Graph RRS feed

  • Question

  • Hello dear TechNet members,

    may someone help me out with getting PowerPivot (Excel 2016) running?

    I want to try the following, having 2 datasets.

    One data set (CompanyRev) includes companies and their annual revenue (for each year a new row). Another data set  (CompanySalesVol) includes the sales volme (also for each year a new row).

    In order to relate them I created a dummy relation "CompanyNameAndID" which stores the unique names of the companies and a new unique identifier. I added these identifiers to the other two files for the respective entries for company&year.

    This worked so far and i could relate the three relations in the Data Model.

    Now I wanted to view the data in a Pivot table in the following form:

    Company XY                       2015          2014           2013         2012         Sum

    - Annual Revenue                130            125             143           150           548

    - Sales Volume                     50               49               55             57            211

    Company XZ

    - Annual Revenue                230            215             290           310            1045

    - Sales Volume                      19               18              25              27             89

    But i cannot display revenue and sales volume in one Pivot Table. Excel somehow is only able to display either years and revenue from the first data set or years and revue from the second data set. It seems not to "understand" that years are the same dimension in both datasets. I also tried it with an alternative relation ""CompanyNameAndYearAndID", where i created an ID for each company and each year and also added these to the respective company and year. But this also did not work out..

    Later on I want to plot the data as a linear line with revenue depending on sales volume, but sofar I did not manage to get there ;)

    I can do this manually with VLOOKUP but that's not what I tried to achieve here.

    Thank you very much already in advance!

    Looking forward to your response,

    Cheers

    Matto


    Sunday, October 29, 2017 6:20 PM

Answers

  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Prepare data for PP with PQ.
    No dummies, no relationships.
    http://www.mediafire.com/file/zxwh4w9uqyya1m6/10_29_17.xlsx
    http://www.mediafire.com/file/p8c9uzs26g1s1uo/10_29_17.pdf

    • Marked as answer by Matto Mattata Monday, October 30, 2017 2:58 PM
    Monday, October 30, 2017 1:12 AM
  • Hi Matto,

    Thanks for your response.

    In this scenario, I would suggest you to combine these two data set (CompanyRev and CompanySalesVol) into one fact table(Company,Year,Revenue,SalesVolume), since they all contain the same dimensions company and year. With this combined fact table, you will be able to achieve your requirement.

    You can use DAX function LookupValue to create calculated column in one of the table, such as create a calculated column called SalesVolume in table CompanyRev as below:

    SalesVolume :=
    LOOKUPVALUE (
        CompanySalesVol[SalesVolume],
        CompanySalesVol[Company], CompanyRev[Company],
        CompanySalesVol[Year], CompanyRev[Year]
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Visakh16MVP Monday, October 30, 2017 7:08 AM
    • Marked as answer by Matto Mattata Monday, October 30, 2017 2:58 PM
    Monday, October 30, 2017 6:11 AM

All replies

  • Excel 2016 Pro Plus with PowerPivot and Power Query (aka Get & Transform)
    Prepare data for PP with PQ.
    No dummies, no relationships.
    http://www.mediafire.com/file/zxwh4w9uqyya1m6/10_29_17.xlsx
    http://www.mediafire.com/file/p8c9uzs26g1s1uo/10_29_17.pdf

    • Marked as answer by Matto Mattata Monday, October 30, 2017 2:58 PM
    Monday, October 30, 2017 1:12 AM
  • Hi Matto,

    Thanks for your response.

    In this scenario, I would suggest you to combine these two data set (CompanyRev and CompanySalesVol) into one fact table(Company,Year,Revenue,SalesVolume), since they all contain the same dimensions company and year. With this combined fact table, you will be able to achieve your requirement.

    You can use DAX function LookupValue to create calculated column in one of the table, such as create a calculated column called SalesVolume in table CompanyRev as below:

    SalesVolume :=
    LOOKUPVALUE (
        CompanySalesVol[SalesVolume],
        CompanySalesVol[Company], CompanyRev[Company],
        CompanySalesVol[Year], CompanyRev[Year]
    )


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Visakh16MVP Monday, October 30, 2017 7:08 AM
    • Marked as answer by Matto Mattata Monday, October 30, 2017 2:58 PM
    Monday, October 30, 2017 6:11 AM
  • Thank you very much for these examples!
    Monday, October 30, 2017 2:56 PM
  • Hi Willson,

    thanks a lot for the advice!! I merged the sheets with VLOOKUP, probably not the most elegant way, but it did the job.

    Do you have an idea why my previous approach did not work? I would like to know it for future and more complex tasks.

    Cheers,

    Matto

    Monday, October 30, 2017 2:58 PM