question

RonTech1 avatar image
0 Votes"
RonTech1 asked emilyhua-msft commented

Excel Pivot Calculated field

Good day,

I have the below pivot in excel

112995-capture.png



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,

office-excel-itpro
capture.png (14.1 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good day @RonTech1
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)

0 Votes 0 ·

@RonTech1
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?

0 Votes 0 ·

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

0 Votes 0 ·
HerbertSeidenberg-6295 avatar image
0 Votes"
HerbertSeidenberg-6295 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

emilyhua-msft avatar image
0 Votes"
emilyhua-msft answered emilyhua-msft commented

@RonTech1

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 ).

113495-image.png


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.


image.png (85.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

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

0 Votes 0 ·