Share of Parent

[thomdeluca]

PowerPivot newbie question.

[TABLE="width: 743"]
<tbody>[TR]
[TD]What is the correct syntax for the measure to calculate the 'share of parent' as in the sample below ("Share of Category/Segment")?

Want to calculate from the lowest level, "Product" Sales as a percent of the Segment Level, and then Segment as a percent of Category?[/TD]
[/TR]
[TR]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 743"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Total
Sales[/TD]
[TD][/TD]
[TD]Share
of
Category/
Segment[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Category[/TD]
[TD][/TD]
[TD]Total Vitamins[/TD]
[TD][/TD]
[TD] $ 1,029,361[/TD]
[TD][/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Segment[/TD]
[TD][/TD]
[TD]Adult Multivitamins[/TD]
[TD][/TD]
[TD] $ 566,149[/TD]
[TD][/TD]
[TD]55%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Product 1[/TD]
[TD][/TD]
[TD] $ 396,304[/TD]
[TD][/TD]
[TD]70%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Product 2[/TD]
[TD][/TD]
[TD] $ 113,230[/TD]
[TD][/TD]
[TD]20%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Product 3[/TD]
[TD][/TD]
[TD] $ 56,615[/TD]
[TD][/TD]
[TD]10%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Segment[/TD]
[TD][/TD]
[TD]Calcium[/TD]
[TD][/TD]
[TD] $ 257,340[/TD]
[TD][/TD]
[TD]25%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Product 1[/TD]
[TD][/TD]
[TD] $ 141,537[/TD]
[TD][/TD]
[TD]55%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Product 2[/TD]
[TD][/TD]
[TD] $ 64,335[/TD]
[TD][/TD]
[TD]25%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Product 3[/TD]
[TD][/TD]
[TD] $ 51,468[/TD]
[TD][/TD]
[TD]20%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Segment[/TD]
[TD][/TD]
[TD]Probiotics[/TD]
[TD][/TD]
[TD] $ 205,872[/TD]
[TD][/TD]
[TD]20%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Product 1[/TD]
[TD][/TD]
[TD] $ 113,230[/TD]
[TD][/TD]
[TD]55%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Product 2[/TD]
[TD][/TD]
[TD] $ 51,468[/TD]
[TD][/TD]
[TD]25%[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Product[/TD]
[TD][/TD]
[TD]Product 3[/TD]
[TD][/TD]
[TD] $ 41,174[/TD]
[TD][/TD]
[TD]20%[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[scottsen]

sadly, there is no easy/generic answer for this. Would be awesome if a future version of powerpivot added something to help with this one.

If we were doing % of total, its pretty easy, right.

[Total] := SUM(Table[Value])
[All Total] := CALCULATE([Total], ALL(Table))
[% of All] := DIVIDE([Total], [All Total])

But, of course, that is not what you asked

So, calculating all products within the same segment:
[All Product Total] := CALCULATE([Total], ALL(Table[Product]))

Compare to [All Total] the difference is ALL(Table[Product]) instead of ALL(Table)... the latter would remove all the Segment filter as well.

[% of Segment] := DIVIDE([Total], [All Product Total]) Would give your lowest level "% of parent".

Now, we just have the trauma of writing a measure that works on both levels.

[% of Parent] := IF (ISFILTERED(Table[Product]), [% of Segment], [% of All])

Which, you know, sounds good to me, but it's not like I tested any of this It reads well at least... "If I have a filter on products, then show the % of the segment, otherwise (for Segment nodes), show the % of total".

This obviously gets ugly if you add more levels in your product hierarchy, which is why I say it would rock if they added something to power pivot next version to help with this relatively common ask.

 

[thomdeluca]

I appreciate the insight! I'll have to give a try (as before this, I didn't even know where to begin).

One question, "Product" is referring to that column in the table, right?

Again, many thanks.

 

[scottsen]

for the ISFILTERED check it would be whatever is "on rows" of the pivot table. So, if they are coming from a separate "product master" table, it would be ProductMaster[ProductName] or whatever.

 

[RoryA]

If you don't actually need the measure for something else, you can just right click the field in the pivot and choose to display values as percent of parent row.