Powerpivot DAX sum based on row value

Question

I am trying to create a running total based on 4 criteria but can't seem to get it to work - sorry very new to both powerpivot and DAX.

I have a Query that has a total of 17 columns - 13 actual data and 4 calculated.

I want to get a cumulative total based on 4 criteria. I haven't even been able to get 1 criteria working so far:

CALCULATE(
    SUM(Tonnes),
    Filter(Query,[Deliveryid]=[Deliveryid])
    )

Basically want to sum all the tonnes for each deliveryid - this is simple in a pivot, but i want to eventually do this for all dates before the date value in the row and for each commodity and delivery address etc.

Any help would be appreciated.

I highly recommend this site: daxpatterns.com It makes it look easy but let me assure it definitely isn't, but it's definitely worth learning. — Nick.McDermaid
Thanks Nick. Looks good. Still very new to DAX so any resource is a good one! — ABowering
Sorry I can't answer your question - still learning myself. But one thing I learnt the hard way: unlike a SQL expression, if you have joined columns, it makes a big difference which column (the column in the fact or the column in your dimension) that you use in your expression. — Nick.McDermaid

Marc Pincince Marc Pincince · Accepted Answer · 2017-09-11T13:15:26

Regarding your specific requirement to sum all the tonnes for each Deliveryid, you're close. Try this: =CALCULATE(SUMX(Table1,[Tonnes]),FILTER(Table1,[Deliveryid]=EARLIER([Deliveryid]))). It will sum the Tonnes for each Deliveryid.

Regarding your follow-on desire to sum all the tonnes for each Deliveryid for the dates preceding the current date, try this: =CALCULATE(SUMX(Table1,[Tonnes]),FILTER(FILTER(Table1,[Deliveryid]=EARLIER([Deliveryid])),[Date]<EARLIER([Date]))).

The table below might help show what these do (I used very long column titles to hopefully make this more understandable):

TotalTonnesForDeliveryid (made with the first code item above) simply totals tonnes by Deliveryid. In the table below, you see the TotalTonnesForDeliveryid for Deliveryid 1 is 91 tonnes ... 38 (from 1/1/2017) + 23 (from 1/3/2017) + 30 (from 1/6/2017).

TotalTonnesForDeliveryBeforeCurrentDate (made with the second code item above) totals tonnes by Deliveryid only for the dates preceding the current date. The reason TotalTonnesForDeliveryBeforeCurrentDate shows blanks for first occurences of Deliveryid is because there was no prior delivery. If more than one delivery has been made for a particular Deliveryid, each following occurence of the Deliveryid shows an accumulation of the previous dates' tonnes. So for Deliveryid 1: the initial delivery date (1/1/2017) shows blank TotalTonnesForDeliveryidBeforeCurrentDate (because there was no previous delivery); the second delivery date (1/3/2017) shows 38 tonnes (which is what was delivered on 1/1/2017); and the third delivery date (1/6/2017) shows 61 tonnes (which is the 38 from the first delivery + the 23 from the second delivery).

Powerpivot DAX sum based on row value

1 Answers