cancel
Showing results for
Did you mean:
Regular Visitor

Cumulative Total

Hi,

I know this topic has been treated but I have a special request.

Thanks to article I found the way to get the cumulative total as a mesure like below :

Budget Corrigé Cumulé =
VAR CurIndex = MAX('S-Curve'[Index])
RETURN
CALCULATE(SUM('S-Curve'[Budget Corrige]);FILTER(ALL('S-Curve');'S-Curve'[Index]<=CurIndex))

There is another colum in is tab : PHASE.

Whe n I select a PHASE value the cumative total is false.

I've tried to filter by PHASE but it doesn't help.

Thanks

12 REPLIES 12
Super User

Re: Cumulative Total

Hey,

you should sightly rearrange your model a little and add an additional table "Index". This table then takes the roll of the date table.

Please have a closer look here: http://www.daxpatterns.com/cumulative-total/

You are using ALL(table) in your calculation, that "overrules" the filter from the phase column, maybe you should also consider to create a phase table, then you're model takes the form of a star schema (dimension tables and fact tables).

From my experience a lot of of calculations become difficult or even impossible, if there is just one table.

Hope this helps

Tom

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Regular Visitor

Re: Cumulative Total

My goal is to get the cumul of "Budget Corrige" and "Dep Reelles" for the first to the last day.

Then I wan to be able to get the result for one "IMPUT:PHASE"

Thanks

Super User

Re: Cumulative Total

It will be very helpful if you can share a simplified Excel Workbook that contains sample data.

Here is a link to a post that describes how to create sample data, if it's not possible to share an Excel workbook:

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Highlighted
Regular Visitor

Pierre

Super User

Re: Cumulative Total

Hey,

sorry for the delay, I had to take care of some other issues 🙂

I guess this does the trick:

```Budget **bleep** =
var maxIndex = calculate(LASTNONBLANK('data'[Index], sum(data[Budget Corrige])))
var minIndex = calculate(FIRSTNONBLANK(all('data'[Index]), sum(data[Budget Corrige])))
return
calculate(
sum('data'[Budget Corrige]), FILTER(all(data[Index]),
'data'[Index] >= minIndex &&
'data'[Index] <= maxIndex)
)```

Here you can find a pbix

Maybe you already know this, but if not it is an excellent read about filtering tables: https://www.sqlbi.com/articles/filtering-tables/

Hope this helps

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Regular Visitor

Re: Cumulative Total

Hi,

I had to adhust your code (filter the sum on table and not column) to get a sum as :

```Budget **bleep** =
var maxIndex = calculate(LASTNONBLANK('data'[Index], sum(data[Budget Corrige])))
var minIndex = calculate(FIRSTNONBLANK(all('data'[Index]), sum(data[Budget Corrige])))
return
calculate(
sum('data'[Budget Corrige]), FILTER(all('data'),
'data'[Index] >= minIndex &&
'data'[Index] <= maxIndex)
)```

When I select a "IMPUT:PHASE" it doesn't give me right total amount.

Sorry to bother you...

Pierre

Super User

Re: Cumulative Total

No problem,

but I have to admit that I do not totally understand what the result should look like

a) when you do not slice by a phase

b) when you slice by a phase

maybe you can provide a very simplistic excel file with two sheets

sheet1 - data

3 phases with 3 to 4 indexes each and corresponding values (budget) including zeros

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Regular Visitor

Re: Cumulative Total

Hi,

Find here the Excel I made.

One sheet : data / the other dynamic cross table to explain you.

a) when you do not slice by a phase --> cumulative total budget and cumulative total expenses

b) when you slice by a phase --> cumulative budget of the selected "phases" and cumulative expenses of the selected "phases"

Pierre

Super User

Re: Cumulative Total

Hey,

not sure if I'm still missing some important aspect 😉 but maybe ...

Here is a link to a new pbix file

And here a little screen shot that looks pretty much the same as from your Excel sheet

Maybe

Hamburg - Germany
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Announcements

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)