Forum Discussion
Conditional Division
Hi, I am stuck in a situation please guide me
in below mention data which Have ID targets and achievement of KPI's I have create a pivot table as mention below and have created three measures
=sum(Table1[Tgt])
=sum(Table1[Ach])
=DIVIDE([Ach1],[Target],0)*100
now I need to check Ach % in a way that in all KPI if >=120 should be 120 else same division except E KPI it should be check on if >=105 overwrite it to 105 else same division. Raw data is mention below thanks.
Pivot Table Raw Data
Column Labels ABCDEFGRow LabelsTargetAch1Ach%TargetAch1Ach%TargetAch1Ach%TargetAch1Ach%TargetAch1Ach%TargetAch1Ach%TargetAch1Ach%Grand Total28303366119070151556127145789739964592676241542355905966221046386137380979183484410591
| Month | All | ||||||||||||||||||||
| 6002 | 378 | 372 | 98 | 0 | 0 | 0 | 8326035 | 8433663 | 101 | 3588260 | 2910900 | 81 | 83 | 77 | 93 | 9 | 12 | 133 | 6781347 | 6108129 | 90 |
| 6006 | 788 | 875 | 111 | 0 | 1 | 0 | 37626223 | 37261201 | 99 | 18631217 | 16374726 | 88 | 223 | 232 | 104 | 25 | 31 | 124 | 11321124 | 10241049 | 90 |
| 6013 | 711 | 878 | 123 | 0 | 0 | 0 | 44428378 | 38749888 | 87 | 6733595 | 5983420 | 89 | 102 | 153 | 150 | 10 | 16 | 160 | 8419966 | 7601940 | 90 |
| 6015 | 953 | 1241 | 130 | 0 | 6 | 0 | 61175491 | 61344987 | 100 | 16973690 | 16273309 | 96 | 188 | 160 | 85 | 19 | 27 | 142 | 11575481 | 10892987 | 94 |
Raw Data
Date|ID|KPI|MONTH|TGT|Ach
| 01/05/2018 00:00 | 6002 | A | May | 378 | 372 |
| 01/05/2018 00:00 | 6006 | A | May | 788 | 875 |
| 01/05/2018 00:00 | 6013 | A | May | 711 | 878 |
| 01/05/2018 00:00 | 6015 | A | May | 953 | 1241 |
| 01/05/2018 00:00 | 6002 | B | May | 0 | 0 |
| 01/05/2018 00:00 | 6006 | B | May | 0 | 1 |
| 01/05/2018 00:00 | 6013 | B | May | 0 | 0 |
| 01/05/2018 00:00 | 6015 | B | May | 0 | 6 |
| 01/05/2018 00:00 | 6002 | C | May | 8326035 | 8433663 |
| 01/05/2018 00:00 | 6006 | C | May | 37626223 | 37261201 |
| 01/05/2018 00:00 | 6013 | C | May | 44428378 | 38749888 |
| 01/05/2018 00:00 | 6015 | C | May | 61175491 | 61344987 |
| 01/05/2018 00:00 | 6002 | D | May | 3588260 | 2910900 |
| 01/05/2018 00:00 | 6006 | D | May | 18631217 | 16374726 |
| 01/05/2018 00:00 | 6013 | D | May | 6733595 | 5983420 |
| 01/05/2018 00:00 | 6015 | D | May | 16973690 | 16273309 |
| 01/05/2018 00:00 | 6002 | E | May | 83 | 77 |
| 01/05/2018 00:00 | 6006 | E | May | 223 | 232 |
| 01/05/2018 00:00 | 6013 | E | May | 102 | 153 |
| 01/05/2018 00:00 | 6015 | E | May | 188 | 160 |
| 01/05/2018 00:00 | 6002 | F | May | 9 | 12 |
| 01/05/2018 00:00 | 6006 | F | May | 25 | 31 |
| 01/05/2018 00:00 | 6013 | F | May | 10 | 16 |
| 01/05/2018 00:00 | 6015 | F | May | 19 | 27 |
| 01/05/2018 00:00 | 6002 | G | May | 6781347 | 6108129 |
| 01/05/2018 00:00 | 6006 | G | May | 11321124 | 10241049 |
| 01/05/2018 00:00 | 6013 | G | May | 8419966 | 7601940 |
| 01/05/2018 00:00 | 6015 | G | May | 11575481 | 10892987 |
Hi marmaghan,
Based on your data you shared, we can create a measure to achieve your goal.
Measure = IF(MAX(Table1[KPI])="E"&& [Ach%]>=105,105,IF([Ach%]>=120 && MAX(Table1[KPI])<>"E",120,[Ach%]))
Also please find the pbix as attached.
Regards,
Frank
2 Replies
Hi marmaghan,
Based on your data you shared, we can create a measure to achieve your goal.
Measure = IF(MAX(Table1[KPI])="E"&& [Ach%]>=105,105,IF([Ach%]>=120 && MAX(Table1[KPI])<>"E",120,[Ach%]))
Also please find the pbix as attached.
Regards,
Frank
Thanks for reply it is working fine now