Running Total Help

[knotty150]

Hi All

I'm new to Powerpivot/DAX; have been scouring the internet for some assistance with some calculations I'm struggling with (which should be really simple), but am none the wiser so thought I'd post here....

I'm trying to perform a really basic running total calculation. If you imagine I have just 2 columns in my dataset and am looking to calculate the "Accumulate" column:

Product | Price | Accumulate
Beans | £1 | £1
Beans | £1 | £2
Eggs | £1.50 | £1.50
Beans | £1 | £3
Eggs | £1.50 | £3
Eggs | £1.50 | £4.50


How would I go about doing the above? I'm guessing I may have to order the data first.

Also, I'd like to be able create a summary table of the above data sample which would display 1line/product and calculate the total Price. Any ideas?

Thanks in advance!

Rich

 

[GDRIII]

Create a base measure that is the sum of Accumulate. [Sum of Accumulate]:=SUM(tlbWhatever[Accumulate])

Then you could use that new measure [Sum of Accumulate] CALCULATE in conjunction with ALL for your running total. [Sum of Accumulate TOTAL]:=CALCULATE([Accumulate],ALL(tblWhatever[Accumulate])

That ALL is going to remove the Filter context on the Pivot.

Since you are new, best place to learn would be Rob Collie's book DAX Formulas for PowerPivot...Chapter 9

 

[scottsen]

Does your data have some "order"? Or are you going to accumulate by time intervals (with a calendar table?). You can do what you want in either a calculated column or measure (called calculated field in 2013), but I think I might need more info -- like how you expect to use this in a pivot table, or something

 

[knotty150]

Thanks for the replies - I'm yet to dissect your suggestion GDRIII but I'll be taking a look at it this morning!

scottsen - I'm hoping to use the calculation(s) to assist in calculating a weighted average by product. Once I've calculated the sum of price by product, I'd like to then divide each individual records "Price" by the relevant "Sum of Price" (thus calculating what % of the sub-total, each record is). Does that make sense? Once I've calculated this weighted average, I'd then like to plot the results on a PivotChart or display them in a PivotTable.

What I'm finding is I can't really make much sense of the usual internet articles which attempt to explain the syntax of DAX (probably just me being thick). I'm hoping with just 1 thoroughly explained, relevant calculation, I'll then be able to dissect and decipher how to utilise DAX in later calculations.

Thanks for your help.

Rich

 

[knotty150]

scottsen - forgot to mention sorry, the data at present has no order and I don't intend to accumulate by a date interval - just looking for sum of price by product. Not sure if it makes a difference but, I'd like the results to update based on the values of a timeline I set against a subsequent PivotChart I create using the data from the calculation.

 

[scottsen]

So, my problem is I hear "running total" and I assume it's running based on some dimension (typically time).

That said...

[Sum of Price] := SUM(Products[Price])
[% of Total] := DIVIDE([Sum of Price] / CALCULATE([Some of Price], ALL(Products))

Might get you headed towards to the % of total calculation?