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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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

19 REPLIES 19
Anonymous
Not applicable

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!

Hi, I wanted to do the same except I want it to highlight if its greater than the previous date (I have weekly data). I just flipped the < sign however, its highlighting the first column as well for all rows for some reason, I guess assuming that the date before it is 0. Can you please help me @MFelix 

Hi @ET1995 ,

 

This calculation is based on the year basis so because it uses sameperiodlastyear so the needs is not only to change the signal.

 

Don't know how you have your model setup but you should use something similar to:

CALCULATE (
SUM('Export'[COUNT] ),
FILTER (ALL(DateTable), DateTable[Date] >= Min(DateTable[Date]) - 7 && DateTable[Date] <= Min(DateTable[Date]) - 1
)

 

I'm assuming you are using dates to the week detail on your visualization.

 

If not can you please share the sample of your setup.

 

 

 


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Thanks for your reply @MFelix ! Yes yes I changed the code to suit the weekly period as well so here is a summary of what I'm trying to do

 

So, I am trying to remove the formatting from the first column. This is essentially weekly quantity of a different products, with products on x axis and weeks on y axis. The idea is to highlight the cell if the value is more (green) or less (pink) than the previous week. I created this measure and applied as field value in conditional formatting.


format =
SWITCH (
TRUE (),
SUM(Append1[PDQ] )< CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)), "pink",
SUM(Append1[PDQ] )> CALCULATE (SUM(Append1[PDQ] ),DATEADD(Append1[MinDate],-7,DAY)),"lightgreen")ET1995_0-1619764557402.png

 

 

Hi @ET1995 ,

 

Can you share a sample of your data model?


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix for some reason Power BI is not allowing me to post it here nor am I able to send you a private message. Please advise

Hi @ET1995

 

To share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix its not allowing me to post any link or file for some reason (saying post flooding detected). Can I email you somewhere?

@MFelix Thank you, I have sent you the email

MFelix
Super User
Super User

Hi @Anonymous ,

 

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 Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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
 

Hi @Anonymous ,

 

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 Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

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

Hi @Anonymous ,

 

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 Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@MFelix 

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

Hi @Anonymous ,

 

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 Super User!

Check out my blog: Power BI em Português



Anonymous
Not applicable

@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.

Hi @Anonymous ,

 

This is the same type of information I made my mockup, and it worked, can you share a sample of your file trough private message? With data from both year to check what can be happening?

 

Regards,

MFelix


Regards

Miguel Félix


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

Proud to be a Super User!

Check out my blog: Power BI em Português



Nathaniel_C
Super User
Super User

@Anonymous ,

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!




Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.