Conditional formatting of cells

[elcamino]

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")

[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.

[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,

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 · Accepted solution]

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.

[rohit1991]

Hi@elcamino

The 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.

[elcamino]

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.

[bhanu_gautam]

@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
)
)

[elcamino]

it doesn't work.

Thanks for the message