Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
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"
Thanks
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
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
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
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
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
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
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
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
Can you please provide a Screenshot of your adjusted formula and of the error message, maybe you encountered a bug
Find my answers below.
Some other questions
Probelm is still occuring
Pierre
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |