Division in Matrix Visual

[jkhan]

Hi All,

Please help to create DAX formual for below case. 

1. I have created Matrix with 3 Level of hierarchy

2. Now I need to add column with % So i created below DAX to add this % but i am getting 100% in Matrix.

TOT_ACTUAL = SUM(PBI_MIS_TB[ACTUAL])

TOT_BUDGET = SUM(PBI_MIS_BUDGET[BUDGET])

ACTUAL_% = DIVIDE(SUM(PBI_MIS_TB[ACTUAL]),PBI_MIS_TB[TOT_ACTUAL],0 )

BUDGET_% = DIVIDE(SUM(PBI_MIS_BUDGET[BUDGET]),PBI_MIS_BUDGET[TOT_BUDGET],0 )

3. In this case in top level hierarchy I need   Total/Actual segment Wise

4. In second level I need Second level total to divide with Top level as show in figure. 

5. And so on

6. And so on

Please help with DAX

Thanks & Regards

Jamsher

[jkhan]

 Hi, @Whitewater100

I tried both dax

ACTUAL_1% = DIVIDE(SUM(PBI_MIS_TB[ACTUAL]), CALCULATE(SUM(PBI_MIS_TB[ACTUAL]), ALLSELECTED()))

ACTUAL_2% = DIVIDE(SUMX(PBI_MIS_TB, [ACTUAL]),SUMX(ALLSELECTED(PBI_MIS_TB), [ACTUAL]))

In both case irrresptive to hierarchy each time its getting divided with Total not with immediate hierarchy leve. 

 Thanks & Regards

Jamsher

[v-easonf-msft]

Hi, @jkhan 

You can try using isinscope to get the right hierarchy level and change the values at each levels.

Please refer to this tutorial.

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/ 

Best Regards,
Community Support Team _ Eason

[jkhan]

Great Thanks @Whitewater100 for your reply.

I will test the provided solution and will update shortly.

I also got below DAX through googling. I will try both solutions.
ACTUAL_% = DIVIDE(SUM(PBI_MIS_TB[ACTUAL]), CALCULATE(SUM(PBI_MIS_TB[ACTUAL]), ALLSELECTED()))

Thanks & Best Regards
Jamsher

[Whitewater100 · Accepted solution]

Hi:

If you are happy with:

TOT_ACTUAL = SUM(PBI_MIS_TB[ACTUAL])

TOT_BUDGET = SUM(PBI_MIS_BUDGET[BUDGET])

Share of Actual = DIVIDE(SUMX(PBI_MIS_TB, [ACTUAL]),

SUMX(ALLSELECTED(PBI_MIS_TB), [ACTUAL]))

Share of Budget = DIVIDE(SUMX(PBI_MIS_BUDGET, [BUDGET]),

SUMX(ALLSELECTED(PBI_MIS_BUDGET), [BUDGET]))

I hope this works for you.