Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
In bar chart:
X ray: 'Case Table'[Submit Date]
Y ray: Average Case by PC
Colour Setting:
Dax:
Could you help on this? Thanks!
Solved! Go to 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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
97 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |