% of total by level with drill-down in Power View.

[mozartiano]

Hello guys,

I have a table that contains how much a client paid for an invoyce ([Valor NF]) and which department received that money. However, my company divides a department in 3 hieararchical levels, which are then represented in the workbook by 3 fields named Dept. Lvl. 1, 2 & 3.

Here is my problem:

I am using power view to create a dashboard and I create 3 calculated fields to demonstrate what is the percent that each department earned each month.

Revenue:=SUM(FATURAMENTO[Valor NF])

Revenue Dept:=CALCULATE(SUM([Valor NF]);ALL(FATURAMENTO))

% of ALL:=DIVIDE([Revenue]; [Revenue Dept])

When the table is mounted with Lvl1, 2 and 3 in the lines and Year/month in the columns, I get the desired result, nevertheless, when I enable The Levels to be drilled-down, enhancing the visualization, The total is not recalculated by each level and I get every level as a parcentage of the total for level 1, which does not interest me because the lower the level, the lower the percentage values.

Thanks if anybody can help. I uploaded this test workbook in dropbox, feel motivated to download it and experiment.


https://www.dropbox.com/sh/gra5mk8cvk21wyl/AAAtEgsPwckTT7cFZduCIQ9Pa?dl=0

 

[scottsen]

I need to jump on a client call, but see if this helps?
Clever Hierarchy Handling in DAX – SQLBI

The general point is that "% of parent" is a bit of a pain in power pivot. You kind of need a separate measure for each level, then a way to test for which one to use...

 

[mozartiano]

Hi Scott,

well the article is very good, however the solution provided there just doesn't work.

I did the following calculated field

% of Rev:=IF(ISFILTERED(FATURAMENTO[Dept. Lvl. 3]);SUM(Faturamento[Valor NF])/CALCULATE(SUM(Faturamento[Valor NF]);ALL(FATURAMENTO[Dept, Lvl, 3]));IF(ISFILTERED(FATURAMENTO[Dept, Lvl, 2]);SUM(Faturamento[Valor NF])/CALCULATE(SUM(Faturamento[Valor NF]);ALL(FATURAMENTO[Dept, Lvl, 2]));IF(ISFILTERED(FATURAMENTO[Dept, Lvl, 1]);SUM(Faturamento[Valor NF])/CALCULATE(SUM(Faturamento[Valor NF]);ALL(FATURAMENTO[Dept, Lvl, 1])))))

It follows the solution provided in the article, but I still can get only correct numbers for level 3, levels 1 & 2 keep at 100%. I tried to change the order but it just doesn't work.

Any hint?

Thanks

 

[scottsen]

You are not helping me to like Power View. The measure obviously works in a standard pivot table, and it works in powerview if you use just 1 of your dept level at a time... but once you have more than 1... it hates you.