Calculating totals within dimension

[iraskop]

Searching and searching for this answer on the forum and I'm baffled that I don't see it.

Lets say I have:

Dim1   Dim2   Meas1  [Pct Meas1/Dim1]

A         X         4            4/(4+5)=4/9

A         Y         5            5/9

B         X         2            2/3

B         Z         1            1/3

I need the formula syntax to calculate the denominator of the red column: the subtotals for A and B so I can calculate the percentages within them.

[kazlik]

One option would be something like this.

DIVIDE(Sheet1[Meas1], CALCULATE(SUM(Sheet1[Meas1]),FILTER(ALL(Sheet1),Sheet1[Dim1] = EARLIER(Sheet1[Dim1]))))

[greggyb · Accepted solution]

@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.

[iraskop]

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.

[iraskop]

Thanks again. I need to work with some of this and then see if I still have an issue I can articulate.