Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dlynk196
New Member

Matrix Help

 
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?

 

dlynk196_1-1651060190641.png

 

 

1 REPLY 1
ripach
New Member

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors