Conditional Formatting Against Column Totals

[Rubal_Islam]

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. 

[amitchandak]

@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

[Rubal_Islam]

Hey @amitchandak , I am also trying to do the calculation based on the measure "Diff in Sales %" instead of "Diff in Sales".

[Rubal_Islam]

Hi @amitchandak ,

I am almost there, Can you please help with the below. I think my conditions in the formula are not quite right.

[amitchandak]

@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

[Rubal_Islam]

Hi @amitchandak ,

This is what i am getting. Unfortunately, doesn't seem correct.

[amitchandak]

@Rubal_Islam , Please use the [diff %] measure , that should solve the purpose

[Rubal_Islam]

This is what i am getting now. Appreciate your help on this Amit.

[Rubal_Islam]

Hey @amitchandak , Unfortunately, it doesn't. Did you run a copy at your end? Can you share your PBIX file with me?

[amitchandak · Accepted solution]

@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

[Rubal_Islam]

Hey @amitchandak ,

This worked. I am sure a lotta ppl will get benefitted from this. Thanks again for your time and support. legend.

[Rubal_Islam]

Hi @amitchandak ,

Getting the below error. Any idea?