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

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

  • pierredelareux's avatar
    pierredelareux
    Helper I
    9 years ago

    It is what I'm looking for.

    But I can't set it in mine. Power BI reject telling me there is to many values in [Date]. I have to use an aggregate function.

     

    Which one do you advise me to take ?

     

    Regards,

    Pierre

  • TomMartens's avatar
    TomMartens
    Super User
    9 years ago

    What, ...

     

    You are saying that the Power BI file that uses the reduced dataset of your "made up" Excel file works as you expected, but as soon as you take one of the Formulas, lets say "BUDGET SUM" to your Power BI file

     

    BUDGET SUM = 
CALCULATE(SUM(data[BUDGET]),
	FILTER(ALL('data'[DATE]),'data'[DATE] <= MAX('data'[DATE]))
)

    Power BI Desktop throws an error ??? this is odd.

    I'm using the lastest Power BI Desktop version "2.47.4766.801 64-bit (June 2017)", there has been an update, to the 1st June release due to some errors.

     

    Some other questions

    • I assume that you are using ";" as a separator for formula arguments, did you change the "," (both) to ";"
    • How many rows does your Dataset contain, is it the same as in your data.csv
    • Are you sure that your values in the [Date] column are valid dates

     
    Can you please provide a Screenshot of your adjusted formula and of the error message, maybe you encountered a bug

  • pierredelareux's avatar
    pierredelareux
    Helper I
    9 years ago

    Find my answers below.

     

    Some other questions

    • I assume that you are using ";" as a separator for formula arguments, did you change the "," (both) to ";" YES
    • How many rows does your Dataset contain, is it the same as in your data.csv - 4120 lines
    • Are you sure that your values in the [Date] column are valid dates - I change null values to "01/01/2016"

     

    Probelm is still occuring

    Pierre