SOLVED

Basic power Pivot question (why does the relationship "not work")

%3CLINGO-SUB%20id%3D%22lingo-sub-2649324%22%20slang%3D%22en-US%22%3EBasic%20power%20Pivot%20question%20(why%20does%20the%20relationship%20%22not%20work%22)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2649324%22%20slang%3D%22en-US%22%3E%3CP%3EI%20made%20a%20PowerPivot%20model%20and%20am%20wondering%20why%20PowerPivot%20cannot%20make%20an%20aggregation%20by%20dimension%20(row).%20This%20is%20a%20very%20basic%20question%20but%20it%20would%20help%20me%20a%20lot%20to%20understand%20how%20these%20models%20work%20if%20someone%20could%20give%20me%20a%20quick%20answer.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20the%20simplified%20version%3A%3C%2FP%3E%3CP%3EI%20have%20two%20tables%2C%26nbsp%3BItemVendor%20and%20tItemQties.%3C%2FP%3E%3CP%3EItemVendor%20lists%20SKU's%20and%20their%20Supplier.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bartvana_0-1629009447888.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303305iD1F7254C3514B037%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bartvana_0-1629009447888.png%22%20alt%3D%22bartvana_0-1629009447888.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EtItemQties%20lists%20SKU's%20and%20their%20quantities.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bartvana_1-1629009479725.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303306i4B19E9D4EC1293B7%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bartvana_1-1629009479725.png%22%20alt%3D%22bartvana_1-1629009479725.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20added%20both%20tables%20to%20the%20PowerPivot%20model%2C%20and%20made%20a%20relationship%20between%20the%20SKU%20columns.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bartvana_2-1629009614083.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303307i54150BFFE033453D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bartvana_2-1629009614083.png%22%20alt%3D%22bartvana_2-1629009614083.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EWhat%20I%20fail%20to%20understand%20is%20why%2C%20in%20a%20PowerPivot%20pivot%20table%2C%20the%20relationship%20%22doesn't%20work%22%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bartvana_3-1629010001756.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303308iB5FE38902B1683A1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bartvana_3-1629010001756.png%22%20alt%3D%22bartvana_3-1629010001756.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAll%20suppliers%20get%20the%20full%20total%2C%20instead%20of%20their%20own%20total.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20expected%20result%20would%20have%20been%20of%20course%20this%20(which%20I%20made%20in%20%22standard%22%20Excel%2C%20not%20PowerPivot)%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22bartvana_4-1629010049597.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303309iE1E4EDA982C3112E%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22bartvana_4-1629010049597.png%22%20alt%3D%22bartvana_4-1629010049597.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EMany%20thanks%20in%20advance%20for%20any%20clarification%20you%20can%20give!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2649324%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2649349%22%20slang%3D%22en-US%22%3ERe%3A%20Basic%20power%20Pivot%20question%20(why%20does%20the%20relationship%20%22not%20work%22)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2649349%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F863782%22%20target%3D%22_blank%22%3E%40bartvana%3C%2FA%3E%26nbsp%3BYour%20model%20is%20a%20bad%20example%20since%20both%20tables%20contain%20unique%20SKU's.%20In%20reality%20the%20(fact)%20table%20with%20the%20Quantites%20hold%20multiple%20SKUs%20whereas%20the%20(dimension)%20table%20with%20the%20SKU%20and%20Supplier%20info%20will%20hold%20the%20unique%20SKU's.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20drag%20the%20relationship%20from%20the%20fact%20table%20(the%20many%20side)%20to%20the%20dimension%20table%20(the%20one-side)%20it%20shall%20work.%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-08-15%20at%2009.46.11.png%22%20style%3D%22width%3A%20563px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F303311iA6ABF15C18D86CAF%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-08-15%20at%2009.46.11.png%22%20alt%3D%22Screenshot%202021-08-15%20at%2009.46.11.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2649504%22%20slang%3D%22en-US%22%3ERe%3A%20Basic%20power%20Pivot%20question%20(why%20does%20the%20relationship%20%22not%20work%22)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2649504%22%20slang%3D%22en-US%22%3EThat%20simple%20%3A).%20Fabulous%2C%20thank%20you!%3C%2FLINGO-BODY%3E
Frequent Contributor

I made a PowerPivot model and am wondering why PowerPivot cannot make an aggregation by dimension (row). This is a very basic question but it would help me a lot to understand how these models work if someone could give me a quick answer.

 

Here's the simplified version:

I have two tables, ItemVendor and tItemQties.

ItemVendor lists SKU's and their Supplier.

bartvana_0-1629009447888.png

tItemQties lists SKU's and their quantities.

bartvana_1-1629009479725.png

I added both tables to the PowerPivot model, and made a relationship between the SKU columns.

bartvana_2-1629009614083.png

What I fail to understand is why, in a PowerPivot pivot table, the relationship "doesn't work":

bartvana_3-1629010001756.png

All suppliers get the full total, instead of their own total. 

 

My expected result would have been of course this (which I made in "standard" Excel, not PowerPivot):

bartvana_4-1629010049597.png

Many thanks in advance for any clarification you can give!

5 Replies
best response confirmed by bartvana (Frequent Contributor)
Solution

@bartvana Your model is a bad example since both tables contain unique SKU's. In reality the (fact) table with the Quantites holds non-unique SKUs whereas the (dimension) table with the SKU and Supplier info will hold the unique SKU's.

 

If you drag the relationship from the fact table (the many side) to the dimension table (the one-side) it shall work.

Screenshot 2021-08-15 at 09.46.11.png

That simple :). Fabulous, thank you!

@bartvana 

As variant, you may remove relationship at all and using such measure 

Total QTY:=CALCULATE (
    SUM ( tItemQties[Qty] ),
    TREATAS (
        VALUES ( ItemVendor[SKU] ),
        tItemQties[SKU]
    )
)
Interesting, thank you!

@bartvana 

Above is only to illustrate other possibilities.  From performance point of view physical relationship is always preferable, if use virtual one when better TREATAS.