Calculating percentages including subtotal as a measure in Power BI

user_14206282 0
Viewed 422

I have a dataset of population numbers by age group and gender (male & female). I want to make a matrix table which will show the numbers and percentages for the age groups and gender + column subtotals to show the total Persons (male + female).

I have created a measure to calculate percentage. However, when I apply this formula it is showing the correct percentages for male and female but for persons which is a subtotal of male & female it is adding up the percentages. Can someone please let me know how to get the correct percentages in the subtotal column. Thank you.

Measure = 
DIVIDE(
    sum('LGA ERP'[ERP]), 
    CALCULATE(
        SUM('LGA ERP'[ERP]),
        filter(
            ALLSELECTED('LGA ERP'),
            'LGA ERP'[Sex] = max('LGA ERP'[Sex])
        )
    )
)

Please see attached a copy of my Power BI file - https://1drv.ms/u/s!AubIV2PXG9p4gql5E4XrkIW4uDyY9A?e=HSTcyl

enter image description here

powerbi
1 Answers

Try with this below measure. This will solve your issue.

Measure = 
DIVIDE(
    SUM('LGA ERP'[ERP]), 
    CALCULATE(
        SUM('LGA ERP'[ERP]),
        FILTER(
            ALLSELECTED('LGA ERP'),
            'LGA ERP'[Sex] = MAX('LGA ERP'[Sex]) 
                || 'LGA ERP'[Sex] = MIN('LGA ERP'[Sex])
        )
    ) 
)

Here is the final output-

enter image description here

user_36523450
Related