Forum Discussion
Helper IConditional formatting of cell values in Matrix visual
Hi,
I'm trying to highlight cell background in the Matrix visual where cell value is greater then the previous value in the same row. Is it possible to do in the current version of Power BI? A screenshot of Matrix is attached to this post. Any help is highly appreciated!
Matrix visual
Hi vsteinbahs ,
You can create the following measure:
Measure =
var A = CALCULATE(SUM('Table'[Sales]))
var B = CALCULATE(SUM('Table'[Sales]),DATEADD('Table'[Date],-1,DAY))
return
IF(A > B,"#FFFF00",BLANK())
Then in Matrix Format, choose conditional format:
,
enable Background color and select ‘Advanced controls’:
choose Format by Field value and select previous measure under Based on filed
Here is the demo , please try it:
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
6 Replies
- amitchandak
Super User
You can use dif between this day ve previous days, You need to have a date table for the same.
sales =CALCULATE(SUM(Sales[Sales Amount])) Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day)) Change = [sales]-[Day behind Sales ]To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.
Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin- vsteinbahs
Thanks for your reply, I will check it out early next week and let you know how it goes.
v-yingjlCommunity Support
Hi vsteinbahs ,
You can create the following measure:
Measure =
var A = CALCULATE(SUM('Table'[Sales]))
var B = CALCULATE(SUM('Table'[Sales]),DATEADD('Table'[Date],-1,DAY))
return
IF(A > B,"#FFFF00",BLANK())
Then in Matrix Format, choose conditional format:
,
enable Background color and select ‘Advanced controls’:
choose Format by Field value and select previous measure under Based on filed
Here is the demo , please try it:
Best Regards,
Yingjie Li
If this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- vsteinbahs
Hi v-yingjl ,
Thank you very much for your reply and examples. I applied your suggested technique to my project, but the cell values in the row still didn't get highlighted as I was hoping. Per attached screenshot, you can see that the value of "72,210" on Feb 13th for table "adrmst" is higher then the previous value for the same record on Feb 12th. As a brief background on this project, I'm taking a daily snapshots of the database table row count, and want to hightlight the ones where the value is changing and is higher than on the previous day. I can send you my pbix file, if you don't mind to take a look at it.
Row cell value changes per date
- vsteinbahs
Hello @v-yingjl ,
I figured out where the problem was and your suggested technigue of using a "measure" is working perfectly now. Once again, thank you for your help and solution! I learned quite a bit through this exercise 😀 👍
Here is a screenshot from Matrix visual with conditional formatting of cell values.
- danextianYou may use a time intelligence formula to calculate for the diffference between current and previous day.
DIFF =<measure> - Calculate(<measure>, previousday(table[date]))
in the conditional formatting box, there is an option to select another measure to format by instead of the actual measure being formatted.
