Forum Discussion
Calculating totals within dimension
kazlik, that would work in a Calculated Column, but not as a Measure. There is no row context in a Measure for EARLIER() to refer to.
The following should work as a measure (depends on [Meas1] evaluating correctly at the subtotal level - we can address this if it doesn't):
// DAX // Measure Meas1 = <your Meas1 definition> //Measure Meas1GroupTotal = CALCULATE( [Meas1] ,ALLEXCEPT( 'Dim1Table', 'Dim1Table'[Dim1] ) ) Meas2 = DIVIDE( [Meas1], [Meas1GroupTotal] )ALLEXCEPT() clears all context except for that in the named column(s) in arguments 2-N. It has the benefit of clearing filter or row context, and therefore works equally well in a Measure or Calculated Column.
Resident RockstarSo, in general you won't encounter row context in a measure unless you create a row context with a function like FILTER() or one of the *X() functions.
Measures only have a filter context by default. Every visual only imparts a filter context to a measure - even table and matrix visuals, which have rows displayed, create filter contexts based on the labels on those rows.
I'm not sure I understand your question, but I think you're asking "how do I always get the subtotal for [Level2] in a 5-level hierarchy, regardless of which lower level is displayed?" If this isn't the case please clarify. Note I assume Level 1 is the coarsest grain, and level 5 is the finest detail level.
In this case, the generic representation would be something like this:
// DAX
// Measure
CALCULATE(
<base expression>
,ALL( 'DimensionWithTheHierarchy' )
,VALUES( 'DimensionWithTheHierarchy'[Level2] )
)Filter conditions in CALCULATE() are combined in a logical and such that we clear the context on the dimension, and then selectively apply only the values from [Level2] that exist in whatever the calling context is.
As I'm writing I am thinking of another interpretation that might be more in line with what you're thinking. Do you mean that each of the dimensions Dim1 - Dim4 have a hierarchy, and you always want to retrieve the hierarchy level that is Level2 in that dimension?
This is doable with a measure that looks like this:
// DAX
// Measure
SwitchingMEasure =
SWITCH(
TRUE()
,ISCROSSFILTERED( Dim1[Level2] )
,CALCULATE(
<base expression>
,ALL( 'Dim1' )
,VALUES( 'Dim1'[Level2] )
)
,ISCROSSFILTERED( Dim2[Level2] )
,CALCULATE(
<base expression>
,ALL( 'Dim2' )
,VALUES( 'Dim2'[Level2] )
)
, ....
// And so on for each DimN
)There is no way to do this automatically, or at least none that I know of.
Thanks again. I need to work with some of this and then see if I still have an issue I can articulate.