Conditional Formatting Scenario Help

[bbui11]

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.

The matrix visual looks like this, with a single select slicer for “Site”. In the example below, the slicer is set on Site A.

	Jan-2021	Feb-2021
Packages Received	5	16
Packages Sent	2	4
Sent/Received  % (Measure)	40%	25%

The datasets are as follows:

I have a crosstab table that captures Monthly entries related to different metrics for respective sites.

Site/Metrics Sheet

Month Year	Site	Packages Received	Packages Sent
Jan – 2021	A	5	2
Feb-2021	A	16	4
Jan – 2021	B	3	5
Feb-2021	B	6	8
Jan – 2021	C	20	10
Feb-2021	C	7	13

I need to compare the values above to a sheet that defines the goal for a given metric and site.

Goals Sheet

Site	Metric	Goals
A	Packages Received	8
A	Packages Sent	3
A	Sent/Received  %	30%
B	Packages Received	4
B	Packages Sent	4
B	Sent/Received  %	30%
C	Packages Received	7
C	Packages Sent	10
C	Sent/Received  %	30%

The ideal state would be a the matrix visual showing appropriate conditional formatting based on the metrics, as well as consideration to the site that is selected in the slicer.

Example: Green = Goal is met, Red = Goal is not met

Addtl Notes:

• I initially tried unpivoting the Site/Metrics sheet, but was having trouble formatting the matrix visual, since each metric has a specific data type(integers, decimals, percentages.
• The “Goals” sheet has reoccurring updates, therefore I am trying to avoid hard coding in values for conditional formatting rules within the visual

Any input/help would be greatly appreciated. Thanks!

[v-angzheng-msft]

Hi, @bbui11 

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.

[bbui11]

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 · Accepted solution]

Hi, @bbui11 

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.

[AllisonKennedy]

@bbui11  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.

[bbui11]

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