Cumulative

[ewuchatka]

Hello

I imagine there will be a table (or chart) presenting Sales by period (as cumulative sum). 

I'd like to calculate a cumulative for all people, but also when you select a person from the slicer the cumulative will be just for this person.

There are 15 periods in total and below is just some dummy data for three periods.

As you've seen, some people have multiple rows with figures.

I dont know where to start and will be grateful for your help 🙂

Ewa

Period   Person  Sales

1            Jane     10

1            Jane     11

1            John     13     

1            Tom      5

1            Tom      19

2            Jane      18

2            John      5

2            Tom       20

2            Tom      3

2            Jane      1

3            John      5

3            Jane       11

3            Tom       7

3            Jane       6

3            Tom       11

[Greg_Deckler]

Create a measure that is basically:

Measure = SUM([Sales])

Measures are context aware, meaning that if you display this measure in the context of "all" you will get the total of everyone but if you select a person from a slicer you would get just that one person. Note that you should be able to use the default "sum" aggregation and really not have to create the measure at all for something as simple of a calculation as this.

[ewuchatka]

Hi @Greg_Deckler

Thank you

What about Running Totals?

Sorry, I might have not been clear, but I am looking into running totals, I thought Running Totals are the same as Cumulative, but I realise that Cumulative 🙂

So Period 1 will show Period 1 totals, Period 2 will show Period 1 plus Period 2, Period 3 will show Period 1+2+3.

Thanks

Ewa

[CahabaData]

Some aspects of your post are unclear.  But for a running sum you will definitely need an Index column.  You add that via the Query Editor where there is an Add Column tab.

There could be an overall running sum - or - a running sum by Period which would reset at the start of a new period.  But then you are asking for i.e. Period 3 to include Period 1 & 2 totals - so that isn't clear as to which you actually seek.

Another approach is your Period in Visuals you can set them up to be a hierarchy and you might experiment with that without any custom DAX needed. 

[Greg_Deckler · Accepted solution]

Ah, check out the pattern here:

http://www.daxpatterns.com/cumulative-total/