Forum Discussion

jkhan's avatar
jkhan
Helper III
4 years ago
Solved

Division in Matrix Visual

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

DAX
Need Help
  • Whitewater100's avatar
    Whitewater100
    4 years ago

    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

  • Whitewater100's avatar
    Whitewater100
    Solution Sage
    4 years ago

    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.
  • jkhan's avatar
    jkhan
    Helper III
    4 years ago

    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

  • jkhan's avatar
    jkhan
    Helper III
    4 years ago

     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