CODEWITHSUNDEEP
excelpowerpivotdax
ABowering
ABowering

Reputation: 47

Powerpivot DAX sum based on row value

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.

Upvotes: 0

Views: 3274

Answers (1)

Marc Pincince
Marc Pincince

Reputation: 5192

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

enter image description here

Upvotes: 1

Related Questions