Forum Discussion
GlassShark1
Helper III
Helper IIIHelp with cumulative totals
Not having fun with cumulative totals at all.
I have a fact table with a column for dates ('Snapshot Date') and a column for units sold ('No of Homes').
I've had some success with this:
Running Total for Summary Table =
CALCULATE(
SUM('Fact Table'[No of Homes]),
FILTER(ALLEXCEPT('Fact Table','Fact Table'[Source]),
'Fact Table'[Snapshot Date] <= MAX('Fact Table'[Snapshot Date])))
And it seems to work for a table seperating everything out by category. However if i do the same thing for for each single reference though, it shows that it doesn't carry a unit sold in say 2015 right the way through all columns of the table (imagine the table of years 2010 to 2050 - the unit sold in 2015 would only appear under 2015 column, and not in each and every column after that, presumably as there is no reference for that item in 2016, 2017 and so on in the main table. The columns are either snaphot date itself, or financial year columns from a date table. Both result the same way.
And it seems to work for a table seperating everything out by category. However if i do the same thing for for each single reference though, it shows that it doesn't carry a unit sold in say 2015 right the way through all columns of the table (imagine the table of years 2010 to 2050 - the unit sold in 2015 would only appear under 2015 column, and not in each and every column after that, presumably as there is no reference for that item in 2016, 2017 and so on in the main table. The columns are either snaphot date itself, or financial year columns from a date table. Both result the same way.
I did try a different way of doing this by unpivoting the column, filling in nulls with 0 (so each reference would have its own 'blank' value for each possible year), and then unpivoting again, but it was very cumbersome and memory intensive and i really want to avoid this if there is any alternative.
I've been trying various things for the best part of a day! Please help!
Many thanks 🙂
3 Replies
- amitchandak
Super User
GlassShark1 ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
GlassShark1SourceRefSalesDateNote
X 1 10 31/03/2015 X 1 9 31/03/2016 X 1 8 31/03/2017 X 4 7 31/03/2018 X 5 3 31/03/2019 Y 6 5 31/03/2015 Y
Y7 7 31/03/2016 Y 8 4 31/03/2017 Z 9 2 31/03/2014 Z 10 7 31/03/2016 Z
Z11 4 31/03/2019 Z
Z
Z
Z12 7 31/03/2020 X 100 8 31/03/2014
- GlassShark1
