Matrix Conditional Formatting

[amccallum]

Hello,

I am trying to apply conditional formatting to the below matrix.  I want to apply green when the percentage goes up and red when the percentage goes down.  I do not, however, want the color to continue until the percentage changes.  

I am using two unconnected tables to produce the matrix.  One is a date table with a single column of dates from 1/1/2020 to 3/1/2022.  The increment on the dates is one month.  The second is a data table with the following data.  (Please note instead of writing out the Opportunity ID's in the table below for the matrix above I substituted A,B,C,D, and dummy data).

Below is the measure used in the matrix and I am using the date field as the matrix column:

Measure =

var _lastdate=CALCULATE(MAX('Table'[Start of Month]),FILTER(ALL('Table'),[Opportunityld]=MAX('Table'[Opportunityld])&&[Start of Month]<=MAX('DATE'[Date])))

return CALCULATE(MAX('Table'[Min Prob]),FILTER(ALL('Table'),[Start of Month]=_lastdate&&[Opportunityld]=MAX('Table'[Opportunityld])))

Is there a way to write a conditional formatting measure that will fill in the percentage changes like I am asking?  I have only been able to get the color to spread across all the dates with the same percentages so far and even then it only recognizes when the percentage grows not decreases.

Any help you may be able to offer would be greatly appreciated.

Thanks.

[Anonymous · Accepted solution]

Hi @amccallum ,

I suggest you to try this code to create a color measure.

Color = 
VAR _Change_Date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Value] <> SUM ( 'Table'[Value] )
                && 'Table'[Category] = MAX ( 'Table'[Category] )
                && 'Table'[Date] < MAX ( 'Table'[Date] )
        )
    )
VAR _Change_Value =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Category] = MAX ( 'Table'[Category] )
                && 'Table'[Date] = _Change_Date
        )
    )
RETURN
    IF (
        ISBLANK ( _Change_Value ),
        BLANK (),
        IF ( SUM ( 'Table'[Value] ) > _Change_Value, "Green", "Red" )
    )

Then add this measure into conditional formatting in matrix visual.

Result is as below.

Best Regards,
Rico Zhou

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

[amccallum]

Thank you this works.

[amitchandak]

@amccallum , Using Date table and TI, you can get

This Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('Table'[Qty]), previousday('Date'[Date]))

diff =[This Day] - [Last Day]

You can create a color measure and use that in conditional formatting using field value option

color =
var _change =[This Day]-[Last Day]
return
SWITCH (
TRUE(),
_change > 0, "green",
_change = 0, "blue",
_change < 0, "red"
)

[amccallum]

Thank you for your reply.  I have applied the measures you added, but this is not working.  For some reason the measure This Day and Last Day are only picking up the first day in the matrix and using that for comparison.    Please see picture below.  

I think this is caused by the date table being disconnected from the data table which is needed for the spread of the percentages across the dates.  When I connect the tables the conditional formatting works fine, but the spread disappears.  Do you know how to do the conditional formatting without connecting the tables?