Forum Discussion

Soulus101's avatar
Soulus101
New Member
8 years ago

Dynamic ALL()

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.