Excel Pivot Calculated field

Question

Excel Pivot Calculated field

Rontech10111 161

Good day,

I have the below pivot in excel

Please assist with a formula for the calculated column (% Achieved).

% Achieved is calculated by dividing the Actual by the Target.

This calculation is based on the Ind value. If Ind = 1.5, then we cap the division to 150%, if Ind = 3, then cap the division to 300%.

Please advise,

Many thanks,

Regards,

Lz._ 9,016 Reputation points

2021-07-08T11:51:49.94+00:00

Good day @Rontech10111
Will be glad to have a look at it as long as you upload & share a workbook + indicate if Power Pivot would be an option (in case this wouldn't be doable with calculated field)
Emily Hua-MSFT 27,796 Reputation points

2021-07-09T09:34:55.367+00:00

@Rontech10111
Coud you please explain more about "This calculation is based on the Ind value. If Ind = 1.5, then we cap the division to 150%, if Ind = 3, then cap the division to 300%."?

According to your screenshot, the Ind values 1.5 exist for Afr, Spo and Fus, the Ind value 3 exist for Wal. But there are only Spo and Fus cap the division to 150%, is this because the Actual value is evenly divisible by the Target value or as other reasons?
Rontech10111 161 Reputation points

2021-07-09T10:18:08.553+00:00

Good day

Essentially, if the Ind = 1.5, then this means the result of the division (Actual/Target) is capped at 1.5. Thus if Actual/Target is greater than 1.5, then the result will be fixed at 1.5;
, if the Ind = 3, then this means the result of the division (Actual/Target) is capped at 3. Thus if Actual/Target is greater than 3, then the result will be fixed at 3

Many thanks

Regards

2 answers

Your answer

Lz._ 9,016 Reputation points

2021-07-08T11:51:49.94+00:00

Good day @Rontech10111
Will be glad to have a look at it as long as you upload & share a workbook + indicate if Power Pivot would be an option (in case this wouldn't be doable with calculated field)
Emily Hua-MSFT 27,796 Reputation points

2021-07-09T09:34:55.367+00:00

@Rontech10111
Coud you please explain more about "This calculation is based on the Ind value. If Ind = 1.5, then we cap the division to 150%, if Ind = 3, then cap the division to 300%."?

According to your screenshot, the Ind values 1.5 exist for Afr, Spo and Fus, the Ind value 3 exist for Wal. But there are only Spo and Fus cap the division to 150%, is this because the Actual value is evenly divisible by the Target value or as other reasons?
Rontech10111 161 Reputation points

2021-07-09T10:18:08.553+00:00

Good day

Essentially, if the Ind = 1.5, then this means the result of the division (Actual/Target) is capped at 1.5. Thus if Actual/Target is greater than 1.5, then the result will be fixed at 1.5;
, if the Ind = 3, then this means the result of the division (Actual/Target) is capped at 3. Thus if Actual/Target is greater than 3, then the result will be fixed at 3

Many thanks

Regards

Answer 1

Herbert Seidenberg 1,191

Excel 365 Pro Plus with Power Pivot and Power Query.
With PP Measures, not Calculated Columns/Fields.
https://www.mediafire.com/file/acu05a0briaq4p3/07_08_21.xlsx/file
https://www.mediafire.com/file/f23jrosm6f3dvxk/07_08_21.pdf/file

Answer 2

Emily Hua-MSFT 27,796

@Rontech10111

Thanks for your kind explanation.

I suggest you use a calculated field. Select the cells of pivot table, then go to Analyze tab > Calculation tab > Fields, Items, & Sets > Calculated Fields.
Enter the Field name: % Achieved. Please note, on my test, I call it "Test".
Then enter the formula = IF(AND(Ind<Actual /Target,Ind>0),Ind,Actual /Target ).

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Emily Hua-MSFT 27,796 Reputation points

2021-07-12T07:36:27.647+00:00

@Rontech10111
If you have the chance, please check whether my reply is helpful.
Any quetsions, you may post back.

Share via

Excel Pivot Calculated field

2 answers

Your answer