Hi everybody,

I'm running into some difficulty when trying to present a P&L in % of net sales at subtotal levels.

I have a ragged P&L hierarchy (account dimensions L1 to L8, where my net sales is defined at L3). I want to show all values, including the subtotals, as a % of the specific item “net sales” at the L3 dimension.

When I try to use ‘show values as’ as a % of another metric where all my lowest level accounts contain the total net sales amount, it works for the lowest level (L1) accounts but the subtotals are aggregated (that is: the total net sales amounts that are allocated at the lowest account level (L1) are incorrectly aggregated together, which throws off the %-calculation). For example: I have 10 L1 accounts, each of which contains 100m as net sales. At the subtotal L3 (net sales) level, I then get 100m / 10x100m = 10%, where I would expect to see 100%. 

I also tried to use ‘show values as’ as % of a specific item of the L1 dimension, where I used an item variable that contained all 10 L1 accounts under net sales. But that gives off a configuration issue since the variable contains more than 1 item. 

Can you guys help me? What is the best way to show a P&L as a % of net sales, that also shows the correct percentages at the various subtotal levels?

Thanks for thinking along with me All help is very much appreciated.

Best, Bas