locked
Total by two categories RRS feed

  • Question

  • Here is a table that I have built in Excel PowerPivot. I have at least four columns in this table (key, col1, col2, data).  The key is the primary key for the table. There are multiple rows that share each combination of col1 and col2.

    I am creating a calculated column.  What I need is the Sum(data) for all of the rows in my table that have the same col1 and col2 values as the current row.

    In TSQL it would be something like SUM(data) over (Partition by col1, col2).

    Any clues how to do this in DAX.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, August 29, 2014 5:57 PM

Answers

  • CALCULATE (
        SUM ( Table1[Data] ),
        ALL ( Table1 ),
        Table1[Col1] = EARLIER ( Table1[Col1] ),
        Table1[Col2] = EARLIER ( Table1[Col2] )
    )

    or

    =
    CALCULATE (
        SUM ( Table1[Data] ),
        ALL ( Table1[Key], Table1[Data] )
    )

    or

    =
    CALCULATE (
        SUM ( Table1[Data] ),
        ALLEXCEPT ( Table1, Table1[Col1], Table1[Col2] )
    )

    When we use CALCULATE() in a calculated column, the row context is transformed into filter context for the function we pass as the first argument. Thus, the primary key, col1 value, col2 value, and data value are all treated as filters on the table. You would see a total of all rows where each of these values is a match to the current row. Obviously, with a primary key, there will be no repetition of all 4 of these values.

    To escape this, we must alter the filter context in the second and additional arguments to CALCULATE(). In the first formula, we use ALL() to clear all filter context from our table and the pair of EARLIER() functions to restore the row context of col1 and col2.

    In the second example, we pass only the columns that we want excluded from the filter context to the ALL() function. It may seem strange to include [Data] in the ALL() function, but if we don't, the value would still be considered in the filter context, thus you'd get a total of all rows where [Col1], [Col2], and [Data] match.

    The third example utilizes ALLEXCEPT(), which allows us to specify the columns to maintain in the filter context, instead of those to exclude, as in the ALL().


    • Edited by greggyb Friday, August 29, 2014 7:26 PM clarity
    • Marked as answer by Russ Loski Friday, August 29, 2014 7:42 PM
    Friday, August 29, 2014 7:25 PM
  • =
    CALCULATE (
        SUM ( Table1[Data] ),
        ALLEXCEPT ( Table1, Table1[Col1], Table1[Col2] )
    )

    That is what I needed.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, August 29, 2014 7:42 PM

All replies

  • CALCULATE (
        SUM ( Table1[Data] ),
        ALL ( Table1 ),
        Table1[Col1] = EARLIER ( Table1[Col1] ),
        Table1[Col2] = EARLIER ( Table1[Col2] )
    )

    or

    =
    CALCULATE (
        SUM ( Table1[Data] ),
        ALL ( Table1[Key], Table1[Data] )
    )

    or

    =
    CALCULATE (
        SUM ( Table1[Data] ),
        ALLEXCEPT ( Table1, Table1[Col1], Table1[Col2] )
    )

    When we use CALCULATE() in a calculated column, the row context is transformed into filter context for the function we pass as the first argument. Thus, the primary key, col1 value, col2 value, and data value are all treated as filters on the table. You would see a total of all rows where each of these values is a match to the current row. Obviously, with a primary key, there will be no repetition of all 4 of these values.

    To escape this, we must alter the filter context in the second and additional arguments to CALCULATE(). In the first formula, we use ALL() to clear all filter context from our table and the pair of EARLIER() functions to restore the row context of col1 and col2.

    In the second example, we pass only the columns that we want excluded from the filter context to the ALL() function. It may seem strange to include [Data] in the ALL() function, but if we don't, the value would still be considered in the filter context, thus you'd get a total of all rows where [Col1], [Col2], and [Data] match.

    The third example utilizes ALLEXCEPT(), which allows us to specify the columns to maintain in the filter context, instead of those to exclude, as in the ALL().


    • Edited by greggyb Friday, August 29, 2014 7:26 PM clarity
    • Marked as answer by Russ Loski Friday, August 29, 2014 7:42 PM
    Friday, August 29, 2014 7:25 PM
  • =
    CALCULATE (
        SUM ( Table1[Data] ),
        ALLEXCEPT ( Table1, Table1[Col1], Table1[Col2] )
    )

    That is what I needed.


    Russel Loski, MCT, MCSE Data Platform/Business Intelligence. Twitter: @sqlmovers; blog: www.sqlmovers.com

    Friday, August 29, 2014 7:42 PM
  • I assumed you might have more columns in the table than the four you mentioned, so included the alternatives. Depending on the specific scenario, any of these patterns may be the most appropriate.
    Friday, August 29, 2014 7:50 PM