Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Custom conditional color formatting with slicer

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

 

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

6 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-gizhi-msft
        Community Support
        6 years ago

        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