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 Rockstarkazlik, 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.
Yes, that's it. Thanks.
So Meas1GroupTotal is my subtotal at the Dim1 level. Is there a way to express this more generically? As in give me the row context not for a specific dimension, but for at a particular depth in my dimension list?
Say I have Dim1, Dim2, Dim3, and I want the subtotal at the level of the 2nd from deepest, whatever that is (even if I swap Dim2 for Dim4). Something like this is very easy to do in other tools (such as Tableau), so I'm wondering if there's a technique in DAX.
Come to think of it, what about in-line subtotals (under the column, not as a separate column)?
thanks
- greggyb
So, 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.