How to apply conditional formatting in a matrix based on value comparisons?

[shanerolle]

Hello,

I have a matrix set up to show expenses and budget, per account, for each month. So the columns are based on date, rows are based on account number, and the values of the matrix are expenses and budget.

Is there any way that I can format the matrix to compare the expense amount to the budget amount, and if expense is <90% of the budget then have it display as green, >90% but <100% to show yellow, and >100% to show red? 

I have looked through the conditional formatting rules of the matrix but cannot seem to figure it out.

Any help is appreciated, thanks!

Shane

[Icey · Accepted solution]

Hi @shanerolle ,

If what you want is like the screenshot below, try this:

1. Assume that your Expense value in matrix is a measure.

Expense Measure = SUM('Table'[Expense])

2. Create a condition measure.

Condition = 
VAR Percent_ =
    [Expense Measure] / SUM ( 'Table'[Budget] )
RETURN
    SWITCH (
        TRUE (),
        Percent_ < .9, 1,
        Percent_ >= .9
            && Percent_ < 1, 2,
        Percent_ >= 1, 3
    )

3. Set conditional formatting.

Then you can get what you want.

Best Regards,

Icey

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

[Sachalex]

@Icey 
Thank you very much !
I spend my day looking for this and it finally works in my matrix visual!

[KBO]

Hi @shanerolle ,

 may be this helps 🙂

https://www.youtube.com/watch?v=FgnPIaxpdJ0

I think you need also a DAX Measure with "IF" 🙂

Best,

Kathrin

If this post has helped you, please give it a thumbs up!
Did I answer your question? Mark my post as a solution!

[shanerolle]

Hi @KBO ,

Thank you for the quick response and video suggestion, the video did teach me a few things that I did not already know.

I probably should have been more clear in my original post so I apologize. The expense value in my matrix is calculated from thousands and thousands of smaller expenses in the matrix to display the monthly expense beside the monthly budget, so I need to compare that final calculation that is done in the matrix to the budget that is given for that month. I don't know how I could apply the tile a color value if the expense value I want to compare to the budget is not calculated except for in the matrix. I also would like it to be able to compare if it is calculated by year or other criteria, so I feel like it has to be done based on the matrix rather than giving a specific value a color, but don't know how to go about it, or if it is even possible.

Maybe I am not thinking about this the correct way or I am missing something, so any help is appreciated.