How to write the DAX to calculate the percentage below ?

[pbbeginner]

Good afternoon All,

Please help me to write the DAX to calculate the percentage with the table below?

I have the Report name "FISMA MFA" with Category_Index = 9 and FY24Q4 total value 8. and want to divided by "FISMA Organization Report.." with Category_Index = 1 and FY24Q4 total value 15.

How to write the DAX to tell PowerBI that you take the total value of Category_Index 9 divided by the Total value of Category_Index 1 = percentage.

Thank you so much for your help. 

[pankajnamekar25 · Accepted solution]

@pbbeginner  

If the user wants to calculate the percentage of each value against the total in the matrix

DAX

Percentage Measure =

DIVIDE(

    SUM( 'HQ-ALL Category'[Value] ),

    CALCULATE( SUM( 'HQ-ALL Category'[Value] ), ALL( 'HQ-ALL Category' ) ),

    0

)

This formula calculates each value as a percentage of the overall total.

If they want to calculate the percentage within a specific quarter, they can modify it as:

DAX

Percentage by Quarter =

DIVIDE(

    SUM( 'HQ-ALL Category'[Value] ),

    CALCULATE( SUM( 'HQ-ALL Category'[Value] ), ALLSELECTED( 'HQ-ALL Category'[Quarter] ) ),

    0

)

This ensures that the percentage is calculated within each quarter.

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

[pbbeginner · Accepted solution]

@pankajnamekar25

Thank you so much for your assistance.  You are so awesome!!!

[pankajnamekar25 · Accepted solution]

@pbbeginner  

If the user wants to calculate the percentage of each value against the total in the matrix

DAX

Percentage Measure =

DIVIDE(

    SUM( 'HQ-ALL Category'[Value] ),

    CALCULATE( SUM( 'HQ-ALL Category'[Value] ), ALL( 'HQ-ALL Category' ) ),

    0

)

This formula calculates each value as a percentage of the overall total.

If they want to calculate the percentage within a specific quarter, they can modify it as:

DAX

Percentage by Quarter =

DIVIDE(

    SUM( 'HQ-ALL Category'[Value] ),

    CALCULATE( SUM( 'HQ-ALL Category'[Value] ), ALLSELECTED( 'HQ-ALL Category'[Quarter] ) ),

    0

)

This ensures that the percentage is calculated within each quarter.

Thanks,
Pankaj

If this solution helps, please accept it and give a kudos, it would be greatly appreciated.

[pbbeginner]

Hi @pankajnamekar25

I can calculate the total value of each Category_Index and manually divide each total value of Category_Index each other, such CAT1 (for Category_Index 1) / CAT9 (for Category_Index 9).  Do you have any DAX that can solve this problem in the fastest way?

[pbbeginner]

Good morning @pankajnamekar25 

I am sorry that the result is not correct.  I think it should relate to Category_Index in the DAX.  Can you check the DAX please?

I have the Report name "FISMA MFA" with Category_Index = 9 and FY24Q3 total value 6. and want to divided by "FISMA Organization Report.." with Category_Index = 1 and FY24Q3 total value 12.  So it should be 6/12