Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have a pivot table with a dimension as the columns and the measures as the rows. I want to add a "difference" column that subtracts the "1st" column from the "2nd" column. The references can't be hard coded because the dimensional values change. For example, in the screenshot, it currently shows 2020 and 2021. Next year it will be 2021 and 2022. So, I'm looking to add a column that says "Column 2 minus Column 1"
Thanks for any help!
Craig
Hi, you can check the option to show totals, and use SecondaryDimesnionality to change the calculation of the totals.
If theexpression is sum(Value) and the first column is Max(Year)-1 and the second is Max(Year) you can add an expression like:
If(SecondaryDimensionality()=0
,Sum({<Year={$(=Max(Year))}>}Value)-Sum({<Year={$(=Max(Year)-1)}>}Value)
,Sum(Value)
)