Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
.
Solved! Go to Solution.
Managed to fix it by instead of count using sum in the formale since my table already"counts" the values.
Managed to fix it by instead of count using sum in the formale since my table already"counts" the values.
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThanks 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.
Hi @ET1995 ,
Can you share a sample of your data model?
Regards
Miguel Félix
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
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?
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @MFelix
I have a date table and a shipment_date attribute which contains duplicate dates that have a relationship. Using:
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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @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
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThis 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
Proud to be a Super User!
Check out my blog: Power BI em Português@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")
Proud to be a Super User!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |