Power Pivot Hierarchy Help

%3CLINGO-SUB%20id%3D%22lingo-sub-1785761%22%20slang%3D%22en-US%22%3EPower%20Pivot%20Hierarchy%20Help%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1785761%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%20-%20I%20need%20some%20help!%20I%20am%20trying%20to%20summarize%20data%20in%20a%20pivot%20table%20to%20analyze%20and%20would%20like%20to%20know%20if%20there%20is%20a%20way%20to%20%22Summarize%20Values%20By...%22%20differently%20depending%20on%20the%20level%20of%20the%20hierarchy%20so%20when%20I%20collapse%20the%20pivot%20table%20it%20will%20show%20the%20correct%20data.%20Please%20see%20attached%20image%20and%20explanation%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20Im%20pretty%20sure%20the%20best%20way%20to%20do%20this%20would%20be%20to%20use%20Power%20Pivot%20but%20I%20am%20just%20not%20sure%20how%20to%20go%20about%20it.%20I%20have%20basic%20Power%20Pivot%20and%20DAX%20skills%20at%20this%20point.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWithin%20the%20image%20you%20will%20see%20a%20hierarchy%20with%20fake%20data.%20Manufacturer%2C%20Sub-Brand%201%20and%202%2C%20and%20UPC%20level%20data.%20Within%20the%20UPC%20level%20I%20pulled%20in%20Sub-Brand%20since%20the%20pivot%20table%20was%20not%20calculating%20it%20correctly%20since%20some%20of%20the%20measures%20I'm%20using%20are%20not%20additive.%20I%20would%20like%20to%20know%20if%20there%20is%20a%20way%20to%20'summarize%20value%20as'%20MAX%20for%20the%20Sub-Brand%20level%20but%20but%20SUM%20for%20the%20manufacturer%20level%20within%20the%20hierarchy.%20So%20when%20I%20collapse%20each%20level%20of%20the%20hierarchy%20the%20correct%20data%20will%20show.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advanced.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1785761%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPower%20BI%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Highlighted
Established Member

Hello Everyone - I need some help! I am trying to summarize data in a pivot table to analyze and would like to know if there is a way to "Summarize Values By..." differently depending on the level of the hierarchy so when I collapse the pivot table it will show the correct data. Please see attached image and explanation below.

 

So Im pretty sure the best way to do this would be to use Power Pivot but I am just not sure how to go about it. I have basic Power Pivot and DAX skills at this point. 

 

Within the image you will see a hierarchy with fake data. Manufacturer, Sub-Brand 1 and 2, and UPC level data. Within the UPC level I pulled in Sub-Brand since the pivot table was not calculating it correctly since some of the measures I'm using are not additive. I would like to know if there is a way to 'summarize value as' MAX for the Sub-Brand level but but SUM for the manufacturer level within the hierarchy. So when I collapse each level of the hierarchy the correct data will show. 

 

Thank you in advanced. 

 

 

0 Replies