Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
tryan
Frequent Visitor

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,000,000 row data set.  Here is a simple example of how I'd do this in Excel:

 

Client NumberMonthTransaction AmountNet relationship transaction total per month
1Jan-$100-$300
1Jan-$100-$300
1Jan-$100-$300
1Feb$100$300
1Feb$100$300
1Feb$100$300
2Mar$50$300
2Mar$50$300
2Mar$50$300
2Mar$50$300
2Mar$50$300
2Mar$50$300
3Jan$10$20
3Jan$10$20
3Feb$10$30
3Feb$10$30
3Feb$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!

 

2 ACCEPTED SOLUTIONS
Sean
Community Champion
Community Champion

@tryan

 

Try this...

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

Hope this helps! Smiley Happy

View solution in original post

Sean
Community Champion
Community Champion

@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! Smiley Happy

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

View solution in original post

10 REPLIES 10
Sean
Community Champion
Community Champion

@tryan

 

Try this...

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

Hope this helps! Smiley Happy

jaafer
Frequent Visitor

@Sean

 

Hi, If I have a 100 columns, how can i type all the column names in ALLEXCEPT.  is there any other function . thank you

tryan
Frequent Visitor

That worked perfectly!  Thank you!

Sean
Community Champion
Community Champion

@tryan

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! Smiley Happy

tryan
Frequent Visitor

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 NumberMonthTransaction AmountTransaction TypeNet relationship transaction total per month WHERE transaction type equals Addition or Withdrawal
1Jan-$100Withdrawal-$200
1Jan-$100Stock Sale-$200
1Jan-$100Withdrawal-$200
1Feb$100Addition$200
1Feb$100Addition$200
1Feb$100Stock Purchase$200
2Mar$50Addition$200
2Mar$50Addition$200
2Mar$50Addition$200
2Mar$50Stock Purchase$200
2Mar$50Addition$200
2Mar$50Stock Purchase$200
3Jan$10Stock Purchase$0
3Jan$10Stock Purchase$0
3Feb$10Stock Purchase$10
3Feb$10Stock Purchase$10
3Feb$10Addition$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?

Sean
Community Champion
Community Champion

@tryan

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?

tryan
Frequent Visitor

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.

 

 

tryan
Frequent Visitor

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>…)

Sean
Community Champion
Community Champion

@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! Smiley Happy

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

tryan
Frequent Visitor

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.