Forum Discussion

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

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.

W26_Conditional Formatting Scenario Help.pbix35 KB

AllisonKennedy

Community Champion

5 years ago

Anonymous Do you have a DimSite table to connect the two tables you've mentioned? I am also thinking to unpivot the metrics and have a DimMetric table as well. For your formatting you may need to create each as an explicit measure, or use FORMAT function (that makes it text though) within a SWITCH to change formatting based on SELECTEDVALUE(DimMetric[Metric])

For the conditional formatting, you'll again need Explicit measures that calculate the difference between each metric.

So in total you may need either 2 or 6 measures. One for the values to display in the matrix, and one for the conditional formatting, which takes the value displayed and subtracts the goal.

Let me know if you need further guidance and I can help map it out further.

Anonymous

Not applicable

5 years ago

Hi Allison,

Thank you for your response. I initially did try to unpivot the data set and used the format function to handle each data type. The issue I was running into with that method, was that the % values were not properly evaluating against the goals values (decimals).

I have created a "Color Code" measure that appears to be working. The measure returns a 0,1,2, or 3 value based on the value vs. the goal determined by the filtered Site and row context of the metric.

From there I am applying a conditional formatting rule based on the return value (ex. 3 = green, 2 = yellow, etc.)

It appears to be working so far, but if I run into any obstacles, I may need to revisit the unpivot method.

Thanks again for your input!

Bryan