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

 

Try this...

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

Hope this helps! :smileyhappy:

tryan's avatar
tryan
Frequent Visitor
9 years ago

That worked perfectly!  Thank you!