cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculating totals within dimension

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.

1 ACCEPTED SOLUTION
Resident Rockstar

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

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

5 REPLIES 5
Helper II

One option would be something like this.

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

Resident Rockstar

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

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

Frequent Visitor

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

Resident Rockstar

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.

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors