Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,000,000 row data set. Here is a simple example of how I'd do this in Excel:
Client Number | Month | Transaction Amount | Net relationship transaction total per month |
1 | Jan | -$100 | -$300 |
1 | Jan | -$100 | -$300 |
1 | Jan | -$100 | -$300 |
1 | Feb | $100 | $300 |
1 | Feb | $100 | $300 |
1 | Feb | $100 | $300 |
2 | Mar | $50 | $300 |
2 | Mar | $50 | $300 |
2 | Mar | $50 | $300 |
2 | Mar | $50 | $300 |
2 | Mar | $50 | $300 |
2 | Mar | $50 | $300 |
3 | Jan | $10 | $20 |
3 | Jan | $10 | $20 |
3 | Feb | $10 | $30 |
3 | Feb | $10 | $30 |
3 | Feb | $10 | $30 |
In the Excel table above, I used the SUMIFS formula to calculate column D. The exact formula is: =SUMIFS($C$2:$C$18,$A$2:$A$18,A2,$B$2:$B$18,B2). Taking the first row of data as an example, the formula in cell D2 says "SUM the totals in column C for all rows where the client number (column A) matches the client number in cell A2, and where the Month (column B) matches the month in cell B2. So in this example, cell D2 totals to -$300, because there are three rows with client number 1 in the month of January, and the three transactions add up to -$300. Cells D3 and D4 repeat the same value, because they are calculating the total based on the referenced client number and month in row A3 and B3, and row A4 and B4 as the formula is dragged down (notice how the reference cells are unanchored in the SUMIDS formula).
Now, the issue I have is that DAX will not allow me to create this type of calculated column in a power pivot table. I tried the formula below, and it simply sums my entire 6,000,000 row dataset. Note, the table name in power pivot is "Query", but otherwise I am referring to the same table that you see above:
CALCULATE(
sum([Transaction Amount]),
FILTER(Query,[Client Number]=[Client Number]),
FILTER(Query,[Month]=[Month])
)
This formula just recognizes that all of the client numbers match all of the clients numbers, and all the months match all the months, and it adds every row together. I can write somthing more specific such as:
CALCULATE(
sum([Transaction Amount]),
FILTER(Query,[Client Number]=1),
FILTER(Query,[Month]="Jan")
)
And this will accurately return the sum of only transactions for client number 1 from the month of January (-$300 in the example above). But, I need the formula to be dynamic like the SUMIFS function in Excel that I displayed above. Please help!
Solved! Go to Solution.
Try this...
Column or Measure = CALCULATE ( SUM ( 'Table'[Transaction Amount] ), ALLEXCEPT ( 'Table', 'Table'[Client Number], 'Table'[Month] ) )
Hope this helps!
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!
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!
Hi, If I have a 100 columns, how can i type all the column names in ALLEXCEPT. is there any other function . thank you
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!
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?
How about this?
Column or Measure = CALCULATE ( SUM ( 'Table'[Transaction Amount] ), ALLEXCEPT ( 'Table', 'Table'[Client Number], 'Table'[Month], 'Table'[Transaction Type] ) )
Does this give you expected result?
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.
I've solved my own question. And it kind of works like the SUMIFS formula where I actually need to add two SUMIFS together. here's what I did:
CALCULATE(
SUM(Query[Transaction Amount]),
ALLEXCEPT(Query,Query[Client Number],Query[Month]),
Query[Transaction Type] = "Withdrawal",
)
+
CALCULATE(
SUM(Query[Transaction Amount]),
ALLEXCEPT(Query,Query[Client Number],Query[Month]),
Query[Transaction Type] = "Addition",
)
So the CALCULATE function will accept one criteria at a time when you use the following nomenclature for filters:
CALCULATE(<expression>,<filter1>,<filter2>…)
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!
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |