Conditionally Formatting reflects all bars with same colour

[Dooriya101]

Hi All,

I'm trying to use conditional formatting in bar chart however after I applied all bars shows the same colour.

Purpose:

Have team's average #case by PC, and +- 2% shows green, +- 5% shows yellow, the rest shows red.

Main Table:

Location	Category 1	Key Figure	Value
a	C1	HEAD	2530
a	C2	PC	2000
a	C2	SERVER	13
a	C3	IMAGE	5600
a	C3	TEXT	12000
b	C1	HEAD	126
b	C2	PC	1000
b	C2	SERVER	2
b	C3	IMAGE	2500
b	C3	TEXT	1200
c	C1	HEAD	988
c	C2	PC	500
c	C2	SERVER	3
c	C3	IMAGE	1900
c	C3	TEXT	5000
d	C1	HEAD	1365
d	C2	PC	1500
d	C2	SERVER	8
d	C3	IMAGE	1596
d	C3	TEXT	14000

Case Table:

Case Number	Team	Subbmit Date
1	Team a	2022.1.5
2	Team a	2022.1.5
10	Team a	2022.2.7
32	Team a	2022.3.8
60	Team a	2022.5.15
65	Team a	2022.5.15
66	Team a	2022.6.24
67	Team a	2022.6.24
79	Team d	2022.1.5
80	Team d	2022.1.5
81	Team d	2022.1.8
98	Team d	2022.3.7
99	Team d	2022.4.8
100	Team d	2022.4.8
135	Team d	2022.5.15
136	Team d	2022.6.18
325	Team c	2022.3.15
326	Team c	2022.3.15
327	Team c	2022.4.14
328	Team c	2022.4.14
329	Team c	2022.4.14
347	Team c	2022.5.15
348	Team c	2022.5.15
349	Team c	2022.6.27
350	Team c	2022.6.27
234	Team b	2022.2.5
235	Team b	2022.2.5
236	Team b	2022.2.10
237	Team b	2022.2.10
240	Team b	2022.2.10
241	Team b	2022.2.10
242	Team b	2022.3.7
243	Team b	2022.3.7
281	Team b	2022.5.8
282	Team b	2022.5.8
283	Team b	2022.5.8
284	Team b	2022.6.30

Location Group:

Location	Team
a	Team a
d	Team d
b	Team b
c	Team c

Dax:

Total Case Number = COUNT('Case Table'[Case Number])

Total PC = CALCULATE('Main Table'[Total VALUE], 'Main Table'[Key Figure] = "PC")

Avg Case by PC = [Total Case Number]/[Total PC]

Since Main Table has no time related information, and the total PC is always the same, the Avg Case by PC can only show the average number of each month but I need to manully create a baseline to identify the colour to show the fluctuation. 

So the baseline is the average of [Avg Case by PC] in each month:

Total PC by month =

SUMX(

    VALUES('Case Table'[Month]),

    CALCULATE('Main Table'[Total VALUE],'Main Table'[Key Figure] = "PC"))

Baseline for monthly average number = [Total Case Number]/[Total PC by month]

I've crossed checked and all the number shows correct.

[Avg Case by PC] in each month plus together = [Avg Case by PC]

[Baseline for monthly average number] = average of [Avg Case by PC] in each month

In bar chart:

X ray: 'Case Table'[Submit Date]

Y ray: Average Case by PC

Colour Setting:

Dax:

AVG colour setting =

SWITCH(

    TRUE(),

    [Avg Case by PC] >= [Baseline for monthly average number]*0.98 && [Avg Case by PC] <= [Baseline for monthly average number]*1.02, "Green",

    [Avg Case by PC] >= [Baseline for monthly average number]*0.95 && [Avg Case by PC] <= [Baseline for monthly average number]*1.05, "Orange",

    "Red")

But the result is like below:

Could you help on this? Thanks!

[v-yiruan-msft · Accepted solution]

Hi @Dooriya101 ,

I created a sample pbix file base on your provided data and measures, and I found that the value of measure [Baseline for monthly average number] is same with the ones of measure [Avg Case by PC]. That's why all of bars show the green color. You can try to update the formula of measure [Baseline for monthly average number]  as below and check if it can get your expected result...

Baseline for monthly average number =
VAR _totalcases =
    CALCULATE ( [Total Case Number], ALLSELECTED ( 'Case Table' ) )
VAR _totalpc =
    CALCULATE ( [Total PC], ALLSELECTED ( 'Main Table' ) )
VAR _numofmonth =
    CALCULATE (
        DISTINCTCOUNT ( 'Case Table'[Month] ),
        ALLSELECTED ( 'Case Table' )
    )
RETURN
    DIVIDE ( DIVIDE ( _totalcases, _totalpc ), _numofmonth )

Best Regards