I have a pivot table filtered by year - in this case 2024. The pivot table has 3 columns - month, quantity, refund. I tried to do a calculated field. For example in Jan I had 141 refunds with total value of $23299. I expected to get $$165.24 in the calculate field but I get a DIV error. not sure why. No blanks in the dataset. How do I fix it?
Can you please show us an example of your data and the formula used for the calculated field?
Thank you so much! I've attached a sample of the data and the pivot with the calculated field.
All clear! The field "Booking ref" is a text. Although a pivot table can count the number of booking refs, you can't use that filed in a calculation. A workaround would be to add a column, call it "Count" filled with ones. Use that in the calculated field in stead.
See attached.
Since you did not provide expected results for your moving average, my results are done in Power Pivot with a measure. See the attached file for the presentation and calculation.
@Riny Thank you so much! No wonder my calculated field didn't work.
@ alansidman - I hadn't worked with PowerPivot before but could replicate it. Thank you so much.
Appreciate both of your feedback!
You are welcome Elizabeth.
