Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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!

 

DataDataThe chartThe chart

 

1 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

View solution in original post

6 REPLIES 6
amitchandak
Super User
Super User

@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
Not applicable

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 includedPic 1: All Products included

 

Pic 2: Only product APic 2: Only product A

 

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:

8.PNG

9.PNG

See my attached pbix file.

 

Best Regards,

Giotto

Anonymous
Not applicable

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.

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
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.