Custom conditional color formatting with slicer

[Anonymous]

Hi,

I have a column chart showing Sales Amount by Month for different products. When I create a column chart I want to have different colors based on a condition. But I want this condition to be different for each selected product from product slicer. For example, if nothing selected or all selected then if sales amount is bigger than 700 column will be green if it is below 700 then it becomes red. In all other cases (only A selected, only B selected, or both Selected etc), if sales amount is bigger than 250 then green, otherwise red. Then based on this measure I want to add conditional formatting from field value.

Thanks in advance!

DataThe chart

[amitchandak]

@Anonymous , not very clear. But you can create a color measure based on selection like few given below and conditional formatting you can use "Field" and color measure

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

Refer:https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

[Anonymous]

Hi @amitchandak,

Thanks for the reply. I basically want something like this (The result in pic 1): 

ColorAll = IF(SUM(Sales[Sales Amount ])>500,"green","red")

But when I select A from the slicer, I want the measure evaluate 50 istead of 500. So after filtering down A, it will show green if the Sales Amount is more than 50 and will show red if the Sales Amount is less than 50 (The result is in pic 2): 

ColorA = IF(SUM(Sales[Sales Amount ])>50,"green","red")

 But I do not know who to put the conition of selecting a value from the slicer like: If allselected then ColorAll else if A selected then ColorA.

Pic 1: All Products included

Pic 2: Only product A

[v-gizhi-msft]

Hi,

Please try this color measure:

Measure = 
IF (
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Product] ), ALLSELECTED ( 'Table' ) ) = 5,
    IF ( SUM ( 'Table'[Sales Amount] ) >= 700, "#00FF00", "#FF0000" ),
    IF ( SUM ( 'Table'[Sales Amount] ) >= 250, "#00FF00", "#FF0000" )
)

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[Anonymous]

Hi @v-gizhi-msft ,

Thanks for the reply.

It works fine for current data. But what about when new month is added to the data let´s say May 2020 with new product F. Then the the distinct count of product will change from 5 to 6. Then the number (5) should also change in the measure. It should be dynamic somehow.

[v-gizhi-msft · Accepted solution]

Hi,

Please try this:

Measure = 
IF (
    CALCULATE ( DISTINCTCOUNT ( 'Table'[Product] ), ALLSELECTED ( 'Table' ) )
        = CALCULATE ( DISTINCTCOUNT ( 'Table'[Product] ), ALL ( 'Table' ) ),
    IF ( SUM ( 'Table'[Sales Amount] ) >= 700, "#00FF00", "#FF0000" ),
    IF ( SUM ( 'Table'[Sales Amount] ) >= 250, "#00FF00", "#FF0000" )
)

Best Regards,

Giotto

[Anonymous]

@v-gizhi-msft, Perfect! Thank you!