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!
Highlighted
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!
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

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)