Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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!

 

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
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors