cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
pierredelareux Regular Visitor
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
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
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
pierredelareux Regular Visitor
Regular Visitor

Re: Cumulative Total

Please find belowmy screenshot.

 

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"

 

BI.jpg

 

Thanks

Super User
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:

https://community.powerbi.com/t5/Desktop/Please-use-Table-FromRows-to-create-sample-data/m-p/111557

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
pierredelareux Regular Visitor
Regular Visitor

Re: Cumulative Total

Please find the CSV to download here : Link

 

Pierre

Super User
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
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
pierredelareux Regular Visitor
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
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

 

sheet2 - made up results

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!
Highlighted
pierredelareux Regular Visitor
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
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

 

2017-06-29_10-47-54.png

 

Maybe

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

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

Difinity Conference

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

Top Solution Authors
Top Kudoed Authors (Last 30 Days)