Forum Discussion

Dooriya101's avatar
Dooriya101
Frequent Visitor
3 years ago
Solved

Conditionally Formatting reflects all bars with same colour

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:

LocationCategory 1Key FigureValue
aC1HEAD2530
aC2PC2000
aC2SERVER13
aC3IMAGE5600
aC3TEXT12000
bC1HEAD126
bC2PC1000
bC2SERVER2
bC3IMAGE2500
bC3TEXT1200
cC1HEAD988
cC2PC500
cC2SERVER3
cC3IMAGE1900
cC3TEXT5000
dC1HEAD1365
dC2PC1500
dC2SERVER8
dC3IMAGE1596
dC3TEXT14000

 

Case Table:

Case NumberTeamSubbmit Date
1Team a2022.1.5
2Team a2022.1.5
10Team a2022.2.7
32Team a2022.3.8
60Team a2022.5.15
65Team a2022.5.15
66Team a2022.6.24
67Team a2022.6.24
79Team d2022.1.5
80Team d2022.1.5
81Team d2022.1.8
98Team d2022.3.7
99Team d2022.4.8
100Team d2022.4.8
135Team d2022.5.15
136Team d2022.6.18
325Team c2022.3.15
326Team c2022.3.15
327Team c2022.4.14
328Team c2022.4.14
329Team c2022.4.14
347Team c2022.5.15
348Team c2022.5.15
349Team c2022.6.27
350Team c2022.6.27
234Team b2022.2.5
235Team b2022.2.5
236Team b2022.2.10
237Team b2022.2.10
240Team b2022.2.10
241Team b2022.2.10
242Team b2022.3.7
243Team b2022.3.7
281Team b2022.5.8
282Team b2022.5.8
283Team b2022.5.8
284Team b2022.6.30

 

Location Group:

LocationTeam
aTeam a
dTeam d
bTeam 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!

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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