cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johnlogistic Regular Visitor
Regular Visitor

Matrix conditional formatting if value is lower then previous year then red

Hey guys,

I have a matrix which displays  count of shipments per month for 2018 and 2019 so for example the row for june will contain 2 numbers that of 2018 and 2019.

 

Now if the count of shipments for june 2018 is larger then june 2019 then i want the font color of june 2019 be red indicating this. And I want this to be the case for all months. So all values will be white for example except august 2019 or september 2019 because they had lesser shipments then august or september 2018.

 

Didn't really find an option in the advanced conditional formatting options in the matrix options. Anyone know if this is even possible I feel like this needs to be done with a formula of some sorts. Below is an example of my matrix so in this example august 2019 and september should turn orange #ff8800

.image.png

1 ACCEPTED SOLUTION

Accepted Solutions
Johnlogistic Regular Visitor
Regular Visitor

Re: Matrix conditional formatting if value is lower then previous year then red

Managed to fix it by instead of count using sum in the formale since my table already"counts" the values. 

Condittional formatting =
SWITCH (
TRUE (),
SUM('Export'[COUNT] )
< CALCULATE (
SUM('Export'[COUNT] ),
SAMEPERIODLASTYEAR ( DateTable[Date])
), "#ff8800")
image.png
final result
Extra credit to @MFelix  for all the help provided!

View solution in original post

11 REPLIES 11
Super User IV
Super User IV

Re: Matrix conditional formatting if value is lower then previous year then red

@Johnlogistic ,

Here you go! You need to create a measure and use Format by field value.

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting


Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos are nice too.
Nathaniel

 

 

 

 

Color KPI = If (Max('Table'[Index] )= 2, "#b20000")

color1.PNG

color2.PNG

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User III
Super User III

Re: Matrix conditional formatting if value is lower then previous year then red

Hi @Johnlogistic ,

 

As you refer this should be done with a measure, however you need to take in to attention that this depends also on context of your table. Not really sure if you have a date calculation or a year column only but you need to do something similar to this (assuming you have a calendar table):

 

Condittional formatting =
SWITCH (
    TRUE ();
    COUNT ( Conditional[Quant] )
        <= CALCULATE (
            COUNT ( Conditional[Quant] );
            SAMEPERIODLASTYEAR ( 'calendar'[Date] )
        ); "#ff8800"
)

Then use this measure on the conditional value as a Field value.

 

Regards,

MFelix

 

 


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Johnlogistic Regular Visitor
Regular Visitor

Re: Matrix conditional formatting if value is lower then previous year then red

Hi @MFelix 

I have a date table and a shipment_date attribute which contains duplicate dates that have a relationship. Using:

Condittional formatting =
SWITCH (
TRUE (),
COUNT ( Query1[COUNT] )
<= CALCULATE (
COUNT ( Query1[COUNT] ),
SAMEPERIODLASTYEAR ( DateTable[Date].[Date])
), "#ff8800")
 
In advanced options of conditional formatting based on field value i get the following result:
image.png
So somewhere the calculation is wrong only august and september 2019 should be orange
 
Super User III
Super User III

Re: Matrix conditional formatting if value is lower then previous year then red

Hi @Johnlogistic ,

 

Believe that your issue is regarding the .[DAte] in your formula try the following.

 

Condittional formatting =
SWITCH (
TRUE (),
COUNT ( Query1[COUNT] )
<= CALCULATE (
COUNT ( Query1[COUNT] ),
SAMEPERIODLASTYEAR ( DateTable[Date])
), "#ff8800")

Regards,

MFelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Johnlogistic Regular Visitor
Regular Visitor

Re: Matrix conditional formatting if value is lower then previous year then red

Hi @MFelix  

 

Nah I tried that doesn't make a difference for the result. I'm not sure what makes the higher values appear orange, it seems pretty random but it might be something in the dataset. I will have a look at it, but any suggestions?

EDIT: forgot to mention shipment_date is date/time so for example 24/07/2018 00:00:00 can this cause issues creating a relationship with a date table? That would seem the only obvious explanation.  checked changed to date no changes

Super User III
Super User III

Re: Matrix conditional formatting if value is lower then previous year then red

Hi @Johnlogistic ,

 

Sorry for the question but your calendar table is starting on January 1st and ending on December 31st (can be different years)correct?

 

Regards,

MFelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Johnlogistic Regular Visitor
Regular Visitor

Re: Matrix conditional formatting if value is lower then previous year then red

@MFelix 

Yes it starts at 01-01-2018 ends at 31-12-2019

Super User III
Super User III

Re: Matrix conditional formatting if value is lower then previous year then red

Hi @Johnlogistic ,

 

Based on my experiences should work as expected, can you share a sample of your data? 

 

If it's sensitive data you can send a mockup or send it trough private message.

 

Regards,

MFelix


Regards

Miguel Felix


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Johnlogistic Regular Visitor
Regular Visitor

Re: Matrix conditional formatting if value is lower then previous year then red

@MFelix 

image.png

This is a sample of my query with the shipment_date changed to date instead of date/time dont think it matters though. hopefully this helps.

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

Super User Challenge: Can You Solve These?

Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

Power BI Desktop Update - February 2020

Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors