How to calculate percentage of each category and only selected items in Power BI using DAX

Question

I am working on some education data. There are 4 categories and each of them have got a list of items. I have got the numbers. I have made a Matrix table with the Categories and Items in the Rows and the Numbers as Values. What I want to achieve 2 things

1. Show percentages for each of the items based on the category subtotal
2. "Not stated" and "Not applicable" in each category should not be included to calculate the percentages.

Can someone please show me how to create a new measure using DAX in Power BI to achieve this.

Please see the below example screenshot in Excel for detail. Table example

Here is a Power BI file with the example data - https://1drv.ms/u/s!AubIV2PXG9p4gql0qImMTHvl4ZDAWg?e=2flwnP

I am new to Power BI and DAX. Any help will be greatly appreciated. Thank you

Answer 1

Follow these below steps-

Create this Measure first-

category_total = 

VAR current_row_category = MIN(Education[Category])

RETURN 
CALCULATE(
    SUM(Education[Number]),
    FILTER(
        ALLSELECTED(Education),
        Education[Category] = current_row_category
            && Education[Item] <> "Not stated"
            && Education[Item] <> "Not applicable"
    )
)

Now create the % calculation measure as below-

percentage = 
IF (
    MIN(Education[Item]) = "Not stated" || MIN(Education[Item]) = "Not applicable",
    BLANK(),
    (MIN(Education[Number])/[category_total])
)

Now add the above Percentage measure to Metrics value and the output will be as below-