cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Johnlogistic Resolver I
Resolver I

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 Resolver I
Resolver I

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!




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 Félix


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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





Johnlogistic Resolver I
Resolver I

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 Félix


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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





Johnlogistic Resolver I
Resolver I

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 Félix


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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





Johnlogistic Resolver I
Resolver I

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 Félix


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

Proud to be a Datanaut!

Check out my blog:

Power BI em Português





Johnlogistic Resolver I
Resolver I

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
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

April 2020 Community Highlights

April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors