Forum Discussion

iraskop's avatar
iraskop
Frequent Visitor
10 years ago
Solved

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.

 

 

  • greggyb's avatar
    greggyb
    10 years ago

    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.

5 Replies

  • kazlik's avatar
    kazlik
    Icon for Helper II rankHelper II
    10 years ago

    One option would be something like this.

     

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

    • greggyb's avatar
      greggyb
      Icon for Resident Rockstar rankResident Rockstar
      10 years ago

      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's avatar
        iraskop
        Frequent Visitor
        10 years ago

        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