Conditional Division

[marmaghan]

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

[v-frfei-msft · Accepted solution]

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

[marmaghan]

Thanks for reply it is working fine now