Hello, I'm new here so I hope I'm following conventions! Please shout if I can improve this query in any way.
I am trying to improve a set of inefficient measures, the first of which creates a formula which calculates a measure ignoring the context of either or both of a particular column. My current formula is this:
Underlying Total:= IF([Section]="All",
CALCULATE([Moving Total],ALL(Data[Age],Data[Product])),
IF([Section]="Age",
CALCULATE([Moving Total],ALL(Data[Age])),
CALCULATE([Moving Total],ALL(Data[Product]))))
[Section] evaluates the result of a slicer as either "All","Product" or "Age", [Moving Total] is a Moving Calculation. The next step would be to create a measure [Moving Total]/[Underlying Total], effectively giving me the proportion of the total for a given section of the book.
What I'd like to do is set up a VAR statement to define the variables used in ALL(), but the following fails due to the fact that IF() cannot return a table:
Underlying Total:=
VAR Sel = IF([Section]="All",
ALL(Data[Age],Data[Product]),
IF([Section]="Age",
ALL(Data[Age]),
ALL(Data[Product)))
RETURN
CALCULATE([Moving Total] , Sel)
Does anyone have a way of Defining "Sel" such that it returns the desired context? If this means changing the way I use the [Section] variable I'm all ears. I'm afraid my table manipulation skill is simply not up to this challenge.
