Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
I would like to calculate a measure based on the dimension level in the pivot table
I Have 4 levels for Target that they do not roll-up; Meaning aggregation lower Level do not match with Upper levels
Level1
Level2
Level3
Level4
if the dimension is Level1 the Sum(Column1), If the dimension is Level2 then sum(Column2), if the Dimension is Level3 then Sum(Column3), if the Dimension is Level3 then Sum(Column4)
If you want to have all calculations in one column you can try this pick function:
=pick(Dimensionality(), Sum(Sales), Sum(Sales2), Sum(Sales3), Sum(Sales4) )
or constant calculations for each level:
=pick(Dimensionality(), Sum(total <Product> Sales), Sum(total <Month> Sales))
Level1 Sum(Column1)
Level2 Sum(Column2)
Level3 Sum(Column3)
Level4 Sum(Column4)
If you want to have all calculations in one column you can try this pick function:
=pick(Dimensionality(), Sum(Sales), Sum(Sales2), Sum(Sales3), Sum(Sales4) )
or constant calculations for each level:
=pick(Dimensionality(), Sum(total <Product> Sales), Sum(total <Month> Sales))