locked
DAX Sum based on Filter for every row RRS feed

  • Question

  • Hello, I am trying to sum a column based on certain filters for every row in the table. The formula gives the total, but ignores the [Comp#] and [Date] filters- $

    • 1,212,904 is the sum for all Companies and all Dates where [WD Balance]<>0
    • The formula should have given $0 for both 8/31/2013 Dates
    • The formula should have given $4,348 for both 9/30/2013 Dates

    Would someone please help by providing the correct syntax?

    Code:
    =CALCULATE(
                               SUM([Commission]), 
                               FILTER(
                                              tCommPerMonth,
                                                 tCommPerMonth[Comp#]  =  RELATED(Data[Comp#]) 
                                              && tCommPerMonth[Date]   =  [Date] 
                                              && [WD Balance]          <> 0 
                                              && ISBLANK([WD Balance]) =  FALSE()
                                              )
                               )

    The picture below is directly from PowerPivot.



    Expected Output: (Commissions for CO1022 where [WD Balance]<>0 by date)

    Date Commission
    9/30/2013 $4,348
    10/31/2013 $2,860
    11/30/2013 $2,768
    12/31/2013 $5,819
    1/31/2014 $2,138
    2/28/2014 $1,784
    3/31/2014 $13,920
    4/30/2014 $1,748
    5/31/2014 $1,314
    6/30/2014 $12,836
    7/31/2014 $650
    8/31/2014 $626
    9/30/2014 $10,883
    10/31/2014 $134
    11/30/2014 $129
    12/31/2014 $9,267
    1/31/2015 $134
    2/28/2015 $121
    3/31/2015 $765
    4/30/2015 $129
    5/31/2015 $134
    6/30/2015 $741
    7/31/2015 $134
    8/31/2015 $134
    9/30/2015 $707
    10/31/2015 $134
    11/30/2015 $129
    12/31/2015 $722
    1/31/2016 $134
    2/29/2016 $125
    3/31/2016 $129
    Total $75,598


    Thank you in advance!

    Thursday, March 27, 2014 7:20 PM

Answers

  • I'm a little confused about the shape of your model and names of your tables but if say your sample table from PowerPivot is called 'Table1' then the following should get you what you want.

    As a Calculated Column (like your Test column above), this should work:

    =IF(
       Table1[WD Balance] <> 0 
       && NOT(ISBLANK(Table1[WD Balance])), 
       CALCULATE(
          SUM([Commission]), 
          ALLEXCEPT(
              Table1, 
              Table1[Date], 
              Table1[Comp#]
          )
       ), 
       BLANK()
    )

    If you wanted such a calculation to work as a Measure, you would have to do it a little differently:

    Measure Version:=
    SUMX(
       FILTER(
          ALLEXCEPT(
             Table1, 
             Table1[Comp#], 
             Table1[Date]
          ), 
          Table1[WD Balance] <>0 
          && NOT(ISBLANK(Table1[WD Balance]))
          ), 
       CALCULATE(
          SUM(Table1[Commission])
       )
    )

    Thursday, March 27, 2014 8:32 PM
    Answerer