Forum Discussion
Conditional formatting of cells
I have a table visualization with columns with sales per month – from January until December. My goal is to show the values of sales based on the slice filter. This slice filter is related to the type of sale: Sales X, Sales Y and Sales Z. I want my table visualization to show the cells as red when I select Sales X, blue when I select sales Y and red or blue when I select Sales Z, that is, when I select the Sales Z if the values came from Sales X should be red while if it came from sales Y should be blue. Right now, it is working when I select Sales X and Sales Y. However, when I select sales Z shows the right results, but not the right colors. Specifically, in some cases it shows the result from sales X and the cells are blue, while in other cases shows the result from sales Y and the cells are red.
This is my code:
The slice filter is based on this datatable
Sale Type =
DATATABLE(
"Sale Type", STRING,
{
{"Sales X"},
{"Sales Y"},
{"Sales Z"}
}
)
Additionally, I have a column called Sales Z
Sales Z =
IF(
'Sales'[Sales X] <> 0,
'Sales'[Sales X],
'Sales'[Sales Y],)
Moreover, I have a column called
Sales Value =
VAR SelectedType = SELECTEDVALUE('Sale Type'[Sale Type])
VAR IsAllSelected = ISFILTERED('Sale Type'[Sale Type]) = FALSE || COUNTROWS(VALUES('Sale Type'[Sale Type])) > 1
RETURN
IF(
IsAllSelected,
SUM('Sales'[Sales Z]), -- Default when "All" is selected
SWITCH(
SelectedType,
"Sales X", SUM('Sales'[Sales X]),
"Sales Y", SUM('Sales'[Sales Y]),
"Sales Z", SUM('Sales'[Sales Z]),
BLANK()
)
)
For coloring the cells in the table I use this measure
Color Code =
VAR SelectedType = SELECTEDVALUE('Sale Type'[Sale Type])
VAR IsAllSelected = ISFILTERED('Sale Type'[Sale Type]) = FALSE || COUNTROWS(VALUES('Sale Type'[Sale Type])) > 1
RETURN
IF(
IsAllSelected,
- When "All" is selected, determine color based on hierarchical source
IF(
-- logic to determine if hierarchical comes from X or Y
SUM('Sales'[Sales Y]) > SUM('CommSalesData'[Sales X]),
1, -- Blue for Y source
2 -- Red for X source
),
SWITCH(
SelectedType,
"Sales Y", 1, -- Blue
"Sales X", 2, -- Red
"Sales Z",
-- Determine color based on which source is higher/primary
IF(
SUM('Sales'[Sales Y]) > SUM('Sales '[Sales X]),
1, -- Blue if Sales Y is primary
2 -- Red if Sales X is primary
),
0 -- Default/no color
)
)
Additionally an example of the code of January Sales:
Jan Sales =
CALCULATE([Sales Value], 'Sales'[Month Name] = "January")
Hi elcamino,
I’ve tested your scenario with sample data on my end. The colour formatting is working correctly only non-zero values are highlighted according to the sale type selected, and zero values are not coloured.
The PBIX file is attached for your reference.
Hope this helps
Thank you.
9 Replies
- bhanu_gautam
Super User
elcamino , Try using
Color Code =
VAR SelectedType = SELECTEDVALUE('Sale Type'[Sale Type])
VAR IsAllSelected = ISFILTERED('Sale Type'[Sale Type]) = FALSE || COUNTROWS(VALUES('Sale Type'[Sale Type])) > 1
RETURN
IF(
IsAllSelected,
-- When "All" is selected, determine color based on hierarchical source
IF(
SUM('Sales'[Sales Y]) > SUM('Sales'[Sales X]),
1, -- Blue for Y source
2 -- Red for X source
),
SWITCH(
SelectedType,
"Sales Y", 1, -- Blue
"Sales X", 2, -- Red
"Sales Z",
-- Determine color based on which source is higher/primary
IF(
SUM('Sales'[Sales Y]) > SUM('Sales'[Sales X]),
1, -- Blue if Sales Y is primary
2 -- Red if Sales X is primary
),
0 -- Default/no color
)
)it doesn't work.
Thanks for the message
rohit1991The reason the value for Sales Z is showing correctly but the color is not, is because in your conditional formatting measure, you're only comparing Sales Y and Sales X to decide the color. When "Sales Z" is selected, the measure still uses SUM(Sales[Sales Y]) > SUM(Sales[Sales X]), which doesn’t directly relate to Z. Even though Z is a combination or derived value, it still needs to evaluate which source (X or Y) is contributing more within the Z rows. To fix this, modify your conditional formatting measure so that when Sales Z is selected, it calculates the totals of X and Y but only for rows where Z is not blank. Then compare those and assign the color based on which one is higher.
VAR SelectedType = SELECTEDVALUE('SlicerTable'[Sales Type]) VAR SalesXForZ = CALCULATE(SUM(Sales[Sales X]), NOT(ISBLANK(Sales[Sales Z]))) VAR SalesYForZ = CALCULATE(SUM(Sales[Sales Y]), NOT(ISBLANK(Sales[Sales Z]))) RETURN SWITCH( TRUE(), SelectedType = "Sales X" && SUM(Sales[Sales X]) > SUM(Sales[Sales Y]), 2, SelectedType = "Sales Y" && SUM(Sales[Sales Y]) > SUM(Sales[Sales X]), 1, SelectedType = "Sales Z" && SalesYForZ > SalesXForZ, 1, SelectedType = "Sales Z" && SalesXForZ >= SalesYForZ, 2, 0 )In this
-
1 = Blue (Y is higher)
-
2 = Red (X is higher)
-
0 = Default color Now, even for Sales Z, the color is determined based on which source (X or Y) contributes more. This will fix the issue and make the color logic consistent across all types.
Not working. Thanks anyway.
- rohit1991
Hi elcamino
Revised DAX measure that works at row level and gives consistent results for all types, including "Sales Z":
Color Logic = VAR SelectedType = SELECTEDVALUE('SlicerTable'[Sales Type]) VAR SalesX = Sales[Sales X] VAR SalesY = Sales[Sales Y] VAR SalesZ = Sales[Sales Z] RETURN SWITCH( TRUE(), SelectedType = "Sales X" && SalesX > SalesY, 2, SelectedType = "Sales Y" && SalesY > SalesX, 1, SelectedType = "Sales Z" && NOT(ISBLANK(SalesZ)) && SalesY >= SalesX, 1, SelectedType = "Sales Z" && NOT(ISBLANK(SalesZ)) && SalesX > SalesY, 2, 0 )In this logic:
-
1 = Blue (Y is higher)
-
2 = Red (X is higher)
-
0 = Default or no color
This version ensures that even for Sales Z, the color is based on which source (X or Y) contributes more on each row. It avoids comparing totals and works perfectly in visuals with row-level formatting like tables and matrix visuals.
-
-
v-saisrao-msftCommunity Support
Hi elcamino,
I’ve tested your scenario with sample data on my end. The colour formatting is working correctly only non-zero values are highlighted according to the sale type selected, and zero values are not coloured.
The PBIX file is attached for your reference.
Hope this helps
Thank you.
- v-saisrao-msft
Hi elcamino,
Have you had a chance to review the solution we shared earlier? If the issue persists, feel free to reply so we can help further.
Thank you.
- v-saisrao-msft
Hi elcamino,
Checking in to see if your issue has been resolved. let us know if you still need any assistance.
Thank you.
- v-saisrao-msft
Hi elcamino,
We haven’t heard back from you in a while regarding your issue. let us know if your issue has been resolved or if you still require support.
Thank you.
