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
pierredelareux's avatar
pierredelareux
Helper I
9 years ago

Please find the CSV to download here : Link

 

Pierre

  • 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