Sum formula Pivot table

[markusblaut]

Hello guys,

can someone help me?

• I have a pivot table with dimension "Product"
• in the column I want to see the Quantity, but only from the highest selected year. (so if i select in a filter FY 2016,2017 and 2018) I only want to see the sum of the Quantity of 2018, and if I would select FY 2016 and 2017 I only want to see se sum of the Quantity of 2017, and so on...

if i insert "sum(Quantity)" i get always the sum of all selected FYs. Any idea how that could work?

Many thanks for any help!

Best Markus

[agigliotti]

maybe this:

sum( {< year = {"=max(year)"} >} Quantity )

I hope it helps.

[markusblaut]

thanks for your reply Andrea! Unfortunately your suggested formular gives the same results as sum(Quantity)

[agigliotti]

did you try with this below ?

sum( {< FY = {"=max(FY)"} >} Quantity )

FY field should be an external filter and not a pivot dimension.

[markusblaut]

yes i changed year to FY

[sunny_talwar · Accepted solution]

I think you mean this

Sum({<year = {"$(=max(year))"}>} Quantity)

Sum({<FY = {"$(=max(FY))"}>} Quantity)

[markusblaut]

Thank you Sunny and Andrea!!