powerbi

Calculate percentage from 2 columns

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 = 33.33

numerator = 2 (No + No)

divisor = 6 (Yes + Yes + No + Yes + Yes + No)

Solution

  • you can select the first column in PQ and unpivot other columns

    enter image description here

    enter image description here

    then create a measure

    MEASURE =
COUNTX ( FILTER ( 'Table', 'Table'[Value] = "No" ), 'Table'[Value] )
    / COUNTX (
        FILTER ( 'Table', 'Table'[Value] <> "" && 'Table'[Value] <> "n/a" ),
        'Table'[Value]
    )

    enter image description here