cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shanerolle
Frequent Visitor

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

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

 

1 ACCEPTED SOLUTION
Icey
Community Support
Community Support

Hi @shanerolle ,

 

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

expense.PNG

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.

ex.PNG

ex2.PNG

 

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.

 

View solution in original post

4 REPLIES 4
Icey
Community Support
Community Support

Hi @shanerolle ,

 

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

expense.PNG

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.

ex.PNG

ex2.PNG

 

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
Frequent Visitor

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

KBO
MVP

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
Frequent Visitor

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.

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors