Matrix Help

[dlynk196]

Hi,

I'm new to Power BI, and am struggling with something that may be simple, so I apologize in advance. I applied a Conditional Formating to a Matrix table to shade any # >= 75 Green. Now what I need is to have the subtotal give me the % that is Shaded? I can't seem to figure it out, if it's even possible? Please help. So in the example below, for montth 2 it should be 100%, same for 3 & 4. month 5 should be 66.66%. Any idea how I can get to this?

[ripach]

Hi,

you could use a measure to calculate the share of "active" participants, e.g. like this:

Active Participants Share =
DIVIDE(
    COALESCE(
        COUNTROWS(
            FILTER(
                VALUES(Dim[Employee]),
                [Participation Rate] > 0.75
            )
        ),
        0
    ),
    DISTINCTCOUNT(Dim[Employee])
)

The FILTER-function creates a one-table column with all employees that have a participation rate > 0.75 (might have to be replaced with 75, depending on how you defined the participation rate). The count of this table is then divided by the number of employees. I used the COALESCE-function to make sure that a share of 0% is reported in case no active employee is found (COUNTROWS returns blank in that case).

If you really want to show this number as the subtotal for the participation rate (not sure if I understood that right?), you have to create a new measure that displays the active participants share for the subtotal:

Participation Rate Adjusted =
IF(
    ISINSCOPE(Dim[Employee]),
    [Participation Rate],
    [Active Participants Share]
)

In this example, the participation rate is only shown on single employee level, otherwise the active participants share is displayed. I wouldn't recommend this though, since it might be very hard to understand for the users - but maybe you had a different thing in mind anyway!

Best regards,

Robert