Forum Discussion
colour formating
Hi want a measure that can be used for colour formating based on Parameter and target. In our case some are like if the actual is below the target then it show green background and in some case if actual above the target then red. Below is the sample table.
| Month | Jan-19 | Feb-19 | ||
| Parameter Name | Target | Perfo | Target | Perfo |
| Ab | 5.00% | 6.50% | 5.00% | 4.30% |
| Att | 4.13% | 10.30% | 4.13% | 4.30% |
| JAR | 7.00% | 2.10% | 7.00% | 9.13% |
| ESAT | 100.00% | 100.00% | 100.00% | 89.00% |
| Aay Acc | 90.00% | 97.40% | 90.00% | 67.50% |
| First CR | 85.00% | 94.50% | 85.00% | 45.50% |
| IRT | 75.00% | 65.00% | 75.00% | 85.00% |
Below is the condetion table for referrence.(HB=Higher the better,LB=Lower the better)
| Parameter Name | Check |
| Ab | LB |
| Att | LB |
| JAR | HB |
| ESAT | HB |
| Aay Acc | LB |
| First CR | HB |
| IRT | HB |
Data base table is as below. Perfo is calculated by (Numerator/Denominator) and in the Matrix table we are taking target based on average of target.
| Month | Geo | Country | Parameter Name | Target | Numerator | Denominator |
| January | LATAM | Brazil | Ab | 5.00% | 14421 | 425470 |
| January | LATAM | Jamaica | Ab | 5.00% | 20111 | 504372 |
| January | NA | Canada | Ab | 5.00% | 29028 | 729501 |
| January | NA | USA | Ab | 5.00% | 151441 | 3770164 |
| January | LATAM | Brazil | Att | 4.17% | 15508 | 455330 |
| January | LATAM | Jamaica | Att | 4.13% | 21544 | 539704 |
| January | NA | Canada | Att | 4.30% | 31239 | 780745 |
| January | NA | USA | Att | 4.32% | 162457 | 4034489 |
| January | LATAM | Brazil | JAR | 7.00% | 22254 | 487280 |
| January | LATAM | Jamaica | JAR | 7.00% | 30833 | 577509 |
| January | NA | Canada | JAR | 7.00% | 44853 | 835578 |
| January | NA | USA | JAR | 7.00% | 232729 | 4317316 |
| February | LATAM | Brazil | Ab | 5.00% | 16151 | 404198 |
| February | LATAM | Jamaica | Ab | 5.00% | 22525 | 479154 |
| February | NA | Canada | Ab | 5.00% | 32512 | 693027 |
| February | NA | USA | Ab | 5.00% | 169617 | 3581657 |
| February | LATAM | Brazil | Att | 4.17% | 17370 | 432563 |
| February | LATAM | Jamaica | Att | 4.13% | 24130 | 512718 |
| February | NA | Canada | Att | 4.30% | 34988 | 741708 |
| February | NA | USA | Att | 4.32% | 181953 | 3832766 |
| February | LATAM | Brazil | JAR | 7.00% | 24925 | 462917 |
| February | LATAM | Jamaica | JAR | 7.00% | 34532 | 548634 |
| February | NA | Canada | JAR | 7.00% | 50235 | 793800 |
| February | NA | USA | JAR | 7.00% | 260656 | 4101450 |
| January | LATAM | Brazil | ESAT | 100.00% | 3 | 14 |
| January | LATAM | Jamaica | ESAT | 100.00% | 5 | 7 |
| January | NA | Canada | ESAT | 100.00% | 2 | 11 |
| January | NA | USA | ESAT | 100.00% | 1 | 21 |
| January | LATAM | Brazil | Aay Acc | 90.00% | 4326 | 4534 |
| January | LATAM | Jamaica | Aay Acc | 90.00% | 3456 | 4436 |
| January | NA | Canada | Aay Acc | 90.00% | 5535 | 5876 |
| January | NA | USA | Aay Acc | 90.00% | 5067 | 5564 |
| January | LATAM | Brazil | First CR | 85.00% | 10598.7 | 10654.9 |
| January | LATAM | Jamaica | First CR | 85.00% | 8467.2 | 8650.2 |
| January | NA | Canada | First CR | 85.00% | 13560.75 | 13808.6 |
| January | NA | USA | First CR | 85.00% | 12414.15 | 13075.4 |
| January | LATAM | Brazil | IRT | 75.00% | 17487.855 | 20777.055 |
| January | LATAM | Jamaica | IRT | 75.00% | 13970.88 | 16867.89 |
| January | NA | Canada | IRT | 75.00% | 22375.2375 | 26926.77 |
| January | NA | USA | IRT | 75.00% | 20483.3475 | 25497.03 |
Pelase help.
7 Replies
If I understand your issue correctly then I would create a measure like the one below:
formatting = IF( SELECTEDVALUE( Dim[Check]) = "LB"; IF( // For LB [target] > [Perfo]; "Green"; "Red" ); IF( // For HB [target] < [Perfo]; "Green"; "Red" ) )And then I would make some conditional formatting --> Based on field --> [formatting]
If this works then please mark it as the accepted solution.
Thanks for your reply, the table that i keept as referrence is not part of my data base. It is just for referrence. I need the dax baed on the first table. somthing like if(and([parameter]="Ab"),Perfo<=avg[target]),"Green","Red") so it should be like nexted if. But in Power bi it is tough for make somthing like this. Can you help based on this.
I would make the table a part of you datamodel to make the most efficient measure.
But I guess you can make something like the measure below:
formatting = SWITCH( SELECTEDVALUE(Parameter), "AB", IF( [Perfo] <= [Target], "Green", "Red"), "ATT", IF( [Perfo] <= [Target], "Green", "Red"), "ESAT", IF( [Perfo] > [Target], "Green", "Red"), ETC... "Black" )
