cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

Helper III

## Division in Matrix Visual

Hi All,

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

Thanks & Regards

Jamsher

1 ACCEPTED SOLUTION
Solution Sage

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.
4 REPLIES 4
Helper III

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

Community Support

Hi, @jkhan

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

Best Regards,
Community Support Team _ Eason

Helper III

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

Solution Sage

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors