topic Re: Division in Matrix Visual in DAX Commands and Tips

Division in Matrix Visual

jkhan — Sun, 29 May 2022 15:03:26 GMT

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

Re: Division in Matrix Visual

Whitewater100 — Sun, 29 May 2022 15:12:20 GMT

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.

Re: Division in Matrix Visual

jkhan — Sun, 29 May 2022 15:18:01 GMT

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

Re: Division in Matrix Visual

jkhan — Sun, 29 May 2022 15:31:32 GMT

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

Re: Division in Matrix Visual

v-easonf-msft — Tue, 07 Jun 2022 07:09:23 GMT

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