Forum Discussion
calculate function
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/
Try this...
Column or Measure =
CALCULATE (
SUM ( 'Table'[Transaction Amount] ),
ALLEXCEPT ( 'Table', 'Table'[Client Number], 'Table'[Month] )
)Hope this helps! :smileyhappy:
That worked perfectly! Thank you!
There will be an Excel to PBI webinar by Avi Singh on 3/16/2017 Thursday
https://powerbi.microsoft.com/en-us/blog/community-webinars-feb-23-april-6/
I actually may try to catch this too! :smileyhappy:
Thanks Sean, I'll be sure to check out the webinar. I also have one additional and related question. Using the same example above, say I add a column titled "Transaction Type":
Client Number Month Transaction Amount Transaction Type Net relationship transaction total per month WHERE transaction type equals Addition or Withdrawal 1 Jan -$100 Withdrawal -$200 1 Jan -$100 Stock Sale -$200 1 Jan -$100 Withdrawal -$200 1 Feb $100 Addition $200 1 Feb $100 Addition $200 1 Feb $100 Stock Purchase $200 2 Mar $50 Addition $200 2 Mar $50 Addition $200 2 Mar $50 Addition $200 2 Mar $50 Stock Purchase $200 2 Mar $50 Addition $200 2 Mar $50 Stock Purchase $200 3 Jan $10 Stock Purchase $0 3 Jan $10 Stock Purchase $0 3 Feb $10 Stock Purchase $10 3 Feb $10 Stock Purchase $10 3 Feb $10 Addition $10 Let's say I add one element to my sumifs formula so that I also only want to sum rows that say either Addition or Withdrawal for transaction type. I might add two sumifs formulas together like:
=SUMIFS($C$2:$C$18,$A$2:$A$18,A2,$B$2:$B$18,B2,$D$2:$D$18,"Addition")+SUMIFS($C$2:$C$18,$A$2:$A$18,A2,$B$2:$B$18,B2,$D$2:$D$18,"Withdrawal")
in order to obtain column E.
In DAX, I tried something like:
CALCULATE(
SUM(Query[Transaction Amount]),
ALLEXCEPT(Query,Query[Client Number],Query[Month]),
FILTER(Query,Query[Transaction Type] = "Withdrawal" || Query[Transaction Type] = "Addition")
)
However, when I throw the FILTER function into the CALCULATE function, it basically just overrides ALLEXCEPT. So it still returns a value, but I'm back to square one where it sums every row in the data set that satisifies the transaction type "Addition" or "Withdrawal", but I no longer get distinct totals within each Client Number and Month. Is there a way to combine ALLEXCEPT and FILTER?