What is the DAX formula to achieve this type of summation in my PowerPivot table?

0
votes

I have a PowerPivot table running in Excel 2013 and it gathers its contents from an SQL Query. A simplified extract of the table is shown below (Actual table has more that 150,000 rows):

 ID     Revenue    Quarter   
 145     23,000    Qr2 2014
 469    151,000    Qr1 2014
 478     40,000    Qr2 2014
 587     30,000    Qr3 2014
 643     15,000    Qr3 2014
 698     20,000    Qr1 2014
 812     60,000    Qr2 2014

I need a DAX formula next to the "Quarter" column that will give me the following output:

  ID     Revenue    Quarter   SUMQ 
 145     23,000    Qr2 2014   123,000
 469    151,000    Qr1 2014   171,000
 478     40,000    Qr2 2014   123,000
 587     30,000    Qr3 2014    45,000
 643     15,000    Qr3 2014    45,000
 698     20,000    Qr1 2014   171,000
 812     60,000    Qr2 2014   123,000

Basically, the SUMQ column will be a calculated column and it will need to SUM the respective Quarters from all the rows of the table and report that SUM. When new data will added to the table, the SUMQ column will be updated automatically.

My research pointed me to the SUMX formula in DAX but I'm having a hard time trying to implement it in my model.

2
excelsumpowerpivotdax

2 Answers

0
votes

I don't have enough reputation to comment on the post above, but I'd like to note that ALLEXCEPT() is a cleaner way to implement this logic:

SUMQ=
CALCULATE(
    SUM(MyTable[Revenue])
    ,ALLEXCEPT(MyTable, MyTable[Quarter])
)

ALLEXCEPT() clears the context from every column of the table passed as its first argument, except for those named as arguments 2,n.

0
votes

You can create a calculated column with the following:

=
CALCULATE (
    SUM ( [Revenue] ),
    ALL ( MyTable ),
    MyTable[Quarter] = EARLIER ( MyTable[Quarter] )
)

Here you are calculating the sum of Revenue across the entire table (ALL) where the Quarter value is what it was in the inital row context (EARLIER). This type of pattern is often used for abc analysis. See http://www.daxpatterns.com/abc-classification/