Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Conditional Formatting Scenario Help

Hi, I am trying to apply some conditional formatting on a matrix visual that contains Month- Year as columns, and metrics (Some of which are measures)  as values, with “Show on Rows” turned on.   ...
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, Anonymous 

     

    Keep the result measure unchanged, and change the color measure as follows:

     

    _showResult = 
IF (
    MAX ( 'Goals'[Metric] ) = "Packages Sent",
    SUM ( [Packages Sent] ),
    IF (
        MAX ( 'Goals'[Metric] ) = "Packages Received",
        SUM ( [Packages Received] ),
        FORMAT ( SUM ( [Packages Sent] ) / SUM ( [Packages Received] ), "0%" )
        // SUM ( [Packages Sent] ) / SUM ( [Packages Received] )
    )
)
    _color =
VAR _cpResult =
    IF (
        MAX ( 'Goals'[Metric] ) = "Packages Sent",
        SUM ( [Packages Sent] ),
        IF (
            MAX ( 'Goals'[Metric] ) = "Packages Received",
            SUM ( [Packages Received] ),
            SUM ( [Packages Sent] ) / SUM ( [Packages Received] )
        )
    ) // return IF([_cpResult]>=[goals],"Green","red")
RETURN
    IF ( _cpResult >= [goals], "Green", "red" )

     

    Result:

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

v-angzheng-msft's avatar
v-angzheng-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, Anonymous 

 

Establish a many to many relationship between two tables.
Then create measures as follows:

 

_result = 
IF (
    MAX ( 'Goals'[Metric] ) = "Packages Sent",
    SUM ( [Packages Sent] ),
    IF (
        MAX ( 'Goals'[Metric] ) = "Packages Received",
        SUM ( [Packages Received] ),
        FORMAT ( SUM ( [Packages Sent] ) / SUM ( [Packages Received] ), "0%" )
    )
)
goals = SUM('Goals'[Goals])
_color = IF([_result]>=[goals],"Green","red")

 

Create the matrix visual and apply the conditional format to get the result

Please refer to the attachment below for details. Hope this helps.

 

 

Best Regards,
Community Support Team _ Zeon Zheng


If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Zeon,

Thank you very much for your response. The results and goals seems to be working well for the integer values, but the the % calculations don't appear to be properly color coded. All % measures regardless of value are appearing with a green background.

 

The only way I am getting it to work, is by keeping the values as a decimal number. Do you know of any workarounds to retain the % type display and comparison?

 

Thanks!

Bryan

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi, Anonymous 

     

    Keep the result measure unchanged, and change the color measure as follows:

     

    _showResult = 
IF (
    MAX ( 'Goals'[Metric] ) = "Packages Sent",
    SUM ( [Packages Sent] ),
    IF (
        MAX ( 'Goals'[Metric] ) = "Packages Received",
        SUM ( [Packages Received] ),
        FORMAT ( SUM ( [Packages Sent] ) / SUM ( [Packages Received] ), "0%" )
        // SUM ( [Packages Sent] ) / SUM ( [Packages Received] )
    )
)
    _color =
VAR _cpResult =
    IF (
        MAX ( 'Goals'[Metric] ) = "Packages Sent",
        SUM ( [Packages Sent] ),
        IF (
            MAX ( 'Goals'[Metric] ) = "Packages Received",
            SUM ( [Packages Received] ),
            SUM ( [Packages Sent] ) / SUM ( [Packages Received] )
        )
    ) // return IF([_cpResult]>=[goals],"Green","red")
RETURN
    IF ( _cpResult >= [goals], "Green", "red" )

     

    Result:

    Hope this helps.

     

    Best Regards,
    Community Support Team _ Zeon Zheng


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.