Find a variance in the Pivot Table Columns

[ArtsJ]

Hi,

I'm new and struggling to find the solution here.

I need a 3rd Column that shows the variance, there are multiple measures in my Pivot and I need to find the variance here FY22 vs. FY23.  I tried the ValueList function, but had no success... I might be doing something wrong. 

Another issue (I think)is my FiscalYear Dimension is a drill-down menu set in the backend script. Would that be causing an issue (refer attached screen)? Please help!!

[rubenmarin1]

Hi, if the columns are a vertical dimension the options are:

- Add a totals row and use SecondaryDimensionality() to set a different expression for the totals, like:

If(SecondaryDimensionality()=0
  ,Sum({<FiscalYear={$(=MaxString(FiscalYear))}>} Sales)-Sum({<Year={$(=MaxString(FiscalYear)-1)}>} Sales)
  ,Sum(Sales)
)

And this should be donde for each measure.

- The other option is to create a synthetic FiscalYear value called dDfference, but it also needs to adapt each  measure and also have a negative impact in fiscal year filters.

- Another option could be using a simple table, create all measure labels in an inline table with label and code. Use the label as table dimension, and 2 expressions: one for max fiscal year and another for the previous.

To use dimensions in expressions there could be different options, to give a simple one:

Pick(Match(LabelCode)
  ,ExpressionForMeasureWithCode1
  ,ExpressionForMeasureWithCode2
  ,ExpressionForMeasureWithCode3
  ,ExpressionForMeasureWithCode4
)

With a measure for max fiscal year and another for the previous, you can add the difference as another measure with just: Column(1)-Column(2)