Forum Discussion
Conditional formatting based on by ID average
Raw data I have:
ID | Date | Num
A, Jan-1, 1
A, Jan-2, 2
A, Jan-3, 3
B, Jan-1, 11
B, Jan-2, 22
B, Jan-3, 33
C, Jan-1, 111
C, Jan-2, 222
C, Jan-3, 333
by ID Avg Measure I created:
Avg = var m = FIRSTNONBLANK(Source[ID], "")
return CALCULATE(AVERAGE(Source[Num]), FILTER(ALLSELECTED(Source),m =Source[id]))
Matrix visual I want to create but failed - I want to mark those below by ID avg in red, >= in blue.
| ID | Jan-1 | Jan-2 | Jan-3 | by ID Avg |
| A | 1 | 2 | 3 | 2 |
| B | 11 | 22 | 33 | 22 |
| C | 111 | 222 | 333 | 222 |
Hope it's clear and appreciate your guidance!
Anonymous , Create a color measure like and use that with "field value" option in conditional formatting
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") color = switch ( true(), FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen", FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen", /// Add more conditions "red" )https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-values
3 Replies
Anonymous , Create a color measure like and use that with "field value" option in conditional formatting
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") color = switch ( true(), FIRSTNONBLANK(Table[commodity],"NA") ="commodity1" && sum(Table[Value]) >500,"lightgreen", FIRSTNONBLANK(Table[commodity],"NA") ="commodity2" && sum(Table[Value]) >1000,"lightgreen", /// Add more conditions "red" )https://radacad.com/dax-and-conditional-formatting-better-together-find-the-biggest-and-smallest-numbers-in-the-column
https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting#color-by-color-valuesAnonymous - I did it this way:
Flag = VAR __Num = MAX([Num]) VAR __ID = MAX([ID]) RETURN IF(AVERAGEX(FILTER(ALL('Table (15)'),[ID]=__ID),[Num])>__Num,1,0)PBIX is attached below sig, Table 15, Page 15.
