Calculate percentage from 2 columns

[cghanta]

I have a table UPS with 2 columns

location Maint_1 Maint_2

City1	Yes	n/a
City2	Yes	Yes
City3	No
City4	Yes	No

I need to calculate percentage (Measure in Power BI) of Cities where no maintenance is done from the 2 Maint_1 & Maint_2 columns, ignoring the "n/a" and null/no values.

In the above table, "%Cities with no maint" = 2/6 *100 = 33.33

numerator = 2 (No + No)

divisor = 6 (Yes + Yes + No + Yes + Yes + No)
if all values are Yes or n/a or blank, the answer should be 0.00
Thank you in advance.

[DataNinja777 · Accepted solution]

Hi @cghanta ,

You can calculate the percentage of cities where no maintenance is done from both Maint_1 and Maint_2 columns using a DAX measure in Power BI. The idea is to combine the two columns into a single virtual table, filter out any values that are either "n/a" or blank, and then count how many of the remaining values are "No". This count becomes your numerator. The denominator is the total number of valid values ("Yes" or "No"). Finally, you divide the two and multiply by 100 to get the percentage. Here's how you can write the measure:

%Cities with no maint = 
VAR AllValues =
    UNION (
        SELECTCOLUMNS(UPS, "Maint", UPS[Maint_1]),
        SELECTCOLUMNS(UPS, "Maint", UPS[Maint_2])
    )
VAR Filtered =
    FILTER (
        AllValues,
        NOT (ISBLANK([Maint])) && [Maint] <> "n/a"
    )
VAR NoCount =
    COUNTROWS (
        FILTER (Filtered, [Maint] = "No")
    )
VAR TotalValid =
    COUNTROWS (
        FILTER (Filtered, [Maint] IN {"Yes", "No"})
    )
RETURN
    IF (
        TotalValid = 0,
        0,
        DIVIDE(NoCount, TotalValid) * 100
    )

This will return 33.33 in your example, as there are 2 "No" values out of 6 valid values ("Yes" or "No"). If all entries are either "Yes", "n/a", or blank, the measure will correctly return 0.00.

Best regards,

[cghanta]

Thank you @DataNinja777