Forum Discussion

elcamino's avatar
elcamino
Frequent Visitor
1 year ago
Solved

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 typ...
Need Help
Show and Tell
Tips and Tricks
  • v-saisrao-msft's avatar
    v-saisrao-msft
    1 year ago

    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's avatar
rohit1991
Icon for Super User rankSuper User
1 year ago

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's avatar
elcamino
Frequent Visitor
1 year ago

Not working. Thanks anyway.

 

  • rohit1991's avatar
    rohit1991
    Icon for Super User rankSuper User
    1 year ago

    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.