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/

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.