Forum Discussion
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
.
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")final resultExtra credit to MFelix for all the help provided!
19 Replies
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
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:So somewhere the calculation is wrong only august and september 2019 should be orangeHi 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
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.
NathanielColor KPI = If (Max('Table'[Index] )= 2, "#b20000")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")final resultExtra 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 Anonymous ,
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.
