Forum Discussion

pierredelareux's avatar
pierredelareux
Helper I
9 years ago

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 CurIn...
Need Help
Show and Tell
Tutorial Requests
TomMartens's avatar
TomMartens
Super User
9 years ago

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

 

pierredelareux's avatar
pierredelareux
Helper I
9 years ago

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

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    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

     

    • pierredelareux's avatar
      pierredelareux
      Helper I
      9 years ago

      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

      • TomMartens's avatar
        TomMartens
        Super User
        9 years ago

        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