Forum Discussion
Conditional Formatting Against Column Totals
Hi All,
Need a bit of help with Conditional Formatting against the Column Total (Diff in Sales%). Please Note: Diff in Sales% is a calculated measure.
ideally, This is what I want to show.
- State A conditional formatting 10.04% in red as it is lower than the total 23.92% ( Diff in Sales%)
- Customer 3622 in red under State A, as 1.53% is lower than the total 23.92%
- Customer 4371 in green under State A, As 30.57% is higher than the column total of 23.92%
- Similar formatting to the rest of the state and customer.
I have also added the raw data for the table if that helps.
| State | Customer | CY | LY |
| A | 4,371 | 34,954 | 26,771 |
| B | 3,513 | 72,974 | 58,397 |
| C | 4,043 | 45,763 | 60,046 |
| D | 3,780 | 77,586 | 24,186 |
| E | 4,187 | 76,714 | 30,240 |
| F | 4,244 | 52,074 | 40,187 |
| G | 3,824 | 18,016 | 62,822 |
| A | 3,622 | 65,573 | 64,585 |
| B | 3,403 | 31,904 | 10,726 |
| C | 4,308 | 50,914 | 65,216 |
| D | 4,065 | 75,620 | 33,999 |
| E | 3,865 | 48,671 | 34,936 |
| F | 4,222 | 22,939 | 48,199 |
| G | 3,874 | 64,822 | 59,207 |
| B | 4,371 | 28,203 | 10,038 |
| G | 3,622 | 29,093 | 12,643 |
Thank you all.
Rubal_Islam , Oh I was looking at sub total, The logic was on grand total. I created both sub Total and total of Avg and created color on Total (overall avg). File is attached after signature
11 Replies
Rubal_Islam , You can create a color measure
Color =
var _diff = calculate([diff in sales] , filter(allselected(Table), Table[State] = max(Table[State]) ) )
return
Switch(True() ,
Max(Table[State]) ="A" && [diff in sales] < _diff , "Red" ,
// Add other conditions
)
Use the color measure in conditional formatting using the field value option
Hi amitchandak ,
I am almost there, Can you please help with the below. I think my conditions in the formula are not quite right.
Rubal_Islam , Can create measures only with
calculate([diff in sales] , filter(allselected(Table), Table[State] = max(Table[State]) ) )
and check is that giving correct number to compare
Hey amitchandak , I am also trying to do the calculation based on the measure "Diff in Sales %" instead of "Diff in Sales".
