Forum Discussion

dlynk196's avatar
dlynk196
New Member
4 years ago
Solved

Help with a visualization

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?

 

 

Need Help
  • v-kkf-msft's avatar
    v-kkf-msft
    4 years ago

    Hi dlynk196 ,

     

    Please try the following formula:

     

    Measure = 
VAR total =
    COUNTROWS (
        FILTER (
            VALUES ( 'Table'[Employee Name] ),
            CALCULATE ( SUM ( 'Table'[Value] ) ) >= 75
        )
    )
        / CALCULATE ( DISTINCTCOUNT ( 'Table'[Employee Name] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        ISFILTERED ( 'Table'[Employee Name] ),
        SUM ( 'Table'[Value] ),
        FORMAT ( total, "Percent" )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

3 Replies

  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    Hi dlynk196 ,

     

    Please try the following formula:

     

    Measure = 
VAR total =
    COUNTROWS (
        FILTER (
            VALUES ( 'Table'[Employee Name] ),
            CALCULATE ( SUM ( 'Table'[Value] ) ) >= 75
        )
    )
        / CALCULATE ( DISTINCTCOUNT ( 'Table'[Employee Name] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        ISFILTERED ( 'Table'[Employee Name] ),
        SUM ( 'Table'[Value] ),
        FORMAT ( total, "Percent" )
    )

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

    • dlynk196's avatar
      dlynk196
      New Member
      4 years ago

      Hi,

      I am so thankful for the assistance, but I seem to be stuck on the formula. Where you have 

       ALLSELECTED ( 'Table' ) )

       I begin to get an error, what am I supposed to place for ('Table')?

       

      Thanks!

      • v-kkf-msft's avatar
        v-kkf-msft
        Community Support
        4 years ago

        Hi dlynk196 ,

         

        Please replace "Table" with the name of the table where the [Employee Name] field in the matrix is located.

         

        Because if we just use DISTINCTCOUNT ( 'Table'[Employee Name] ) without ALLSELECTED, it will not count the empty rows. For example, for the first column, the result is 2 / 2 = 100%.

         

        Best Regards,
        Winniz