Advanced Conditional Formatting / Calculations

[Shamatix]

Hi there,

So I have received a task I find somewhat impossible and would love some help.

I have a matrix looking like this: 

Expanded:

What I want to achieve is the following:

1. I dont want any numbers in the "Exceeds" column to be red in the "Category 1", even if they exceed.

2. I want numbers in Category2 to be red if they exceed.

3. I want the "Total number" in the customer row, to be red if the total amount of all the rows exceeds the expected (including the category1 numbers)

Right now as you can see the exceeds number is red on the "Customer" row, I only want that number to be red if a row in "Category2" exceeds the expected OR the TOTAL sum of all the numbers exceeds the TOTAL expected sum.

EDIT:

I made the following in Excel, hopefully it makes more sense what I want to achieve.

There is 4 examples here, I dont mind if the column "Exceeds Expected" says 0, is blank or has a number under "Category1", main thing is it doesnt become red and is still included in the total (customer) row

I hope the above made sense.

[v-henryk-mstf]

Hi @Shamatix ,

According to your description, you can create a measure, apply it to "Conditional Formatting", and then set subtotal to red. The reference is as follows:

Measure = IF(MAX('Table'[Category])="Category1","black","red")

If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

[Shamatix]

Hi there,

Thanks a ton for your answer, however there is a slight misunderstanding I think.

I have added a customer column to your pbix file, but for some reason I cant attach a pbix file in this reply? How did you manage?

Can you do the Category1 "header" black numbers as well? The subitems are great, however the Category1 header is still red?

I have attached an updated pbix file.

The change I did to your data:

Best regards

[v-henryk-mstf]

Hi @Shamatix ,

Do you mean that Category1 header is still black? 

Check the screenshot below, you can attach the pbix file you provided.

Best Regards,
Henry

[Shamatix]

Sorry I cant find that button.

Sadly I still havnt found a way to make the header of category 1 black aswell :S

[v-henryk-mstf · Accepted solution]

Hi @Shamatix ,

According to my understanding, HASONEFILTER can be used to specify the result displayed by subtotal and change the color in the latest version of the desktop, and the conditional formatting function cannot be used for row subtotal.

Matrix: conditional formatting on subtotal rows - Microsoft Power BI Community

You can also post your idea here. If there are too many votes, Microsoft will officially add this feature in the next update. Hope to help you.

Best Regards,
Henry

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

[Shamatix]

Hi there,

No. 

The Category1 numbers are red, eventho the numbers are black.

I want the numbers in the category one to also be black 

The numbers: 14951, 14400, 551 are all red in your pbix file, those I want black (only one Category1) just like it's subitems are black 🙂