Forum Discussion

tryan's avatar
tryan
Frequent Visitor
9 years ago
Solved

calculate function

I need some help understanding how the SUMIFS function works in DAX for power pivot.  I've read that some combination of CALCULATE and FILTER works, but I can't get it to work dynamically with my 6,0...
Need Help
  • Sean's avatar
    Sean
    9 years ago

    tryan

     

    Try this...

    Column or Measure =
CALCULATE (
    SUM ( 'Table'[Transaction Amount] ),
    ALLEXCEPT ( 'Table', 'Table'[Client Number], 'Table'[Month] )
)

    Hope this helps! :smileyhappy:

  • Sean's avatar
    Sean
    9 years ago

    tryan

    Ok I read your post too fast... here it is you were actually very close

    Column / Measure =
CALCULATE (
    SUM ( 'Table'[Transaction Amount] ),
    FILTER (
        ALL ( 'Table'[Transaction Type] ),
        'Table'[Transaction Type] = "Withdrawal"
            || 'Table'[Transaction Type] = "Addition"
    ),
    ALLEXCEPT ( 'Table', 'Table'[Client Number], 'Table'[Month] )
)

    Good Luck! :smileyhappy:

    https://www.sqlbi.com/articles/filter-arguments-in-calculate/

tryan's avatar
tryan
Frequent Visitor
9 years ago

That doesn't quite give me the result I need.  That formula sums within each Client Number, Month, and Transaction Type.  So for example, in January, client 3 made two stock purchase transactions for $10 each.  Your DAX formula would show an amount of $20 in E14 and E15.  However, in the table I pasted above, my column E is ignoring Stock Purchase transactions and Stock Sale transactions.  So I want the sum of all transactions for each client within each month where the transaction type is either Addition or Withdrawal.

 

 

Sean's avatar
Sean
Community Champion
9 years ago

tryan

Ok I read your post too fast... here it is you were actually very close

Column / Measure =
CALCULATE (
    SUM ( 'Table'[Transaction Amount] ),
    FILTER (
        ALL ( 'Table'[Transaction Type] ),
        'Table'[Transaction Type] = "Withdrawal"
            || 'Table'[Transaction Type] = "Addition"
    ),
    ALLEXCEPT ( 'Table', 'Table'[Client Number], 'Table'[Month] )
)

Good Luck! :smileyhappy:

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

  • tryan's avatar
    tryan
    Frequent Visitor
    9 years ago

    Awesome, thanks again! Your method works in addition to my own post that used separate CALCULATE functions for each transaction type of "Addition" and "Withdrawal".  But your solution is simpler since it's more efficient if there are more than two transaction types that I'd need to filter on.