Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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 , 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")
https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/
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.
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
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |