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 ne...
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.

     

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