Forum Discussion
Custom conditional Formatting for matrix per row different threshold for same row by filter on table
Basically i want to have a table with commulative values for some months for some fields that for each value i can control a conditional formating. The problem is that lets say like this:
We have two car Types, and 3 employees George,Maria and Mark
Power Bi report will have one matric for comulative sales per month. We can filter the complete page to check what we have per different carType.
Thresold are on level of CarType and Employee.
So data is like this (i am giving only a smal portion:
| Employee | Type | Sale | Date |
| George | Car1 | € 10,000.00 | 2020-01-01 |
| George | Car1 | € 25,000.00 | 2020-01-10 |
| George | Car1 | € 5,000.00 | 2020-01-03 |
| George | Car1 | € 11,879.00 | 2020-02-01 |
| George | Car1 | € 26,438.00 | 2020-02-10 |
| George | Car1 | € 5,532.00 | 2020-02-03 |
| George | Car1 | € 18,811.00 | 2020-03-01 |
And result sould be like:
Imagine a case with 20 cars and 40 Employees. I do not want to have a measure that for each new car (and probably year) to go in and add a new if case with all the Employees.
I was thinking a way to use either a service inside measure to call it by sending everything it needed in params and get the color, or somehow parse the conditions in (>= ....) like in Expression Language in java.
Is there any solution that i do not code inside measure the exact conditions and the thresold manually?
Hi kyrpav ,
Currently, conditional formatting in matrix is based on all values of the matrix. You cannot apply it based on individual rows. Would you please vote for the similar idea in Power BI forum which has been submitted to improve Power BI and make this feature coming sooner.
As a workaround, I suggest you create a calculated column like:
Calculated column = SWITCH(TRUR(),([Employee] = "Geogre" && Type = "Car1" && [salesamount] > 120K) ||([Employee] = "Geogre" && Type = "Car2" && [salesamount] > 200K),1 ,……………Then create conditional format based on the new column:
For more details, please refer to https://www.cloudfronts.com/conditional-formatting-by-row-in-a-matrix/
Best Regards,
Dedmon Dai
4 Replies
Hi kyrpav ,
Could you please show me your sample data(using a table is fine) and the result that you want(using an image or an excel screenshot is fine)?
Thanks.
Aiolos Zhao
i changes a bit the main post to give you example cause the initial reply was exluded as spam
Hi kyrpav ,
Currently, conditional formatting in matrix is based on all values of the matrix. You cannot apply it based on individual rows. Would you please vote for the similar idea in Power BI forum which has been submitted to improve Power BI and make this feature coming sooner.
As a workaround, I suggest you create a calculated column like:
Calculated column = SWITCH(TRUR(),([Employee] = "Geogre" && Type = "Car1" && [salesamount] > 120K) ||([Employee] = "Geogre" && Type = "Car2" && [salesamount] > 200K),1 ,……………Then create conditional format based on the new column:
For more details, please refer to https://www.cloudfronts.com/conditional-formatting-by-row-in-a-matrix/
Best Regards,
Dedmon Dai
kyrpav , Did not get it completely. Create a complex color formula base don need. example
Color Date = if(FIRSTNONBLANK(Table[date],TODAY()) <today(),"lightgreen","red") if(FIRSTNONBLANK(Table[Value],"true") "true","green","red") Color sales = if(AVERAGE(Sales[Sales Amount])<170,"green","red") Color Year = if(FIRSTNONBLANK(Table[Year],2014) <=2016,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow")) Color = if(FIRSTNONBLANK(Table[Year],2014) <=2016 && AVERAGE(Sales[Sales Amount])<170 ,"lightgreen",if(FIRSTNONBLANK(Table[Year],2014)>2018,"red","yellow")) Color sales = if([Sales Today] -[sales yesterday]>0,"green","red")You can use this in color formatting after using "Field" option
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
