You know this is actually much easier than first thought, and I totally over thought it in the beginning
Check out the solution below
Reverse Cumulative Sales =
CALCULATE( [Total Sales],
FILTER( ALLSELECTED( Dates ),
Dates[Date] >= MIN( Dates[Date] ) ) )
You actually only need to change one simple thing in the formula.
See if you can replicate.
Chrs
Hi Sam,
Thanks for this. I think I can work with that.
HI all
I am trying to figure out how to extend this one more step.
It works fine providing I don’t slice it by months, but if I add a slicer for months it breaks down.
What I am hoping to do is be able to slice it by months, but still see the reverse cumulative total to the end of the year.
For example, if I select Jun in the above example I still need to see $7m, but what actually happens is that it becomes $1M (that month’s sales).
It is probably a matter of modifying total sales to ignore the context of the date slicer but not quite sure how to make it happen.
Thanks
Can you add images of the issues your seeing so that I can view the calculations in the correct context you are needing.
Thanks
Hi Sam,
I have anonymised the data so I can attach the file as well. This may help.
Basically I am taking the last x years number of sales (not interested in total sales at this point, just the number of sales per sales person).
I have created a matrix for sales per month per gender for each sales level
The cumulative is working correctly when I have not sliced by month.
When I slice by month:
What I need is to have the month cumulative remain correct.
Please see attached file
Thanks
Testing.pbix (296.3 KB)
I’m pretty sure this is what you need here
Reverse Cumulative Sales =
CALCULATE( [Number of Sales over time],
FILTER( ALL( Dates[Month Of Year] ),
Dates[Month Of Year] >= MIN( Dates[Month Of Year] )))
As you’re using the month of year as the context in this case then the original formula wasn’t evaluating correctly.
I made these changes and it seems to work well
Yep!
It’s the little things.
That seems to do it.
Thanks for the awesome work!
Hi Sam,
Something I missed before (and should have seen it. Sorry)
The column totals are all wrong.
Normally the fix is easy.
I would do something like
Reverse Cumulative Sales 2 =
VAR CumulativeSales =
CALCULATE(
[Number of Sales over time],
FILTER(
ALL( Dates[Month Of Year] ), // Use ALL rather than ALLSELECTED to allow for month filter exclusion
Dates[Month Of Year] >= MIN( Dates[Month Of Year] )
)
)
VAR SalesTotal =
SUMMARIZE(Sales, Sales[Sales Gender], Sales[Salesman Level],
"Cumulative Total", CumulativeSales)
RETURN
IF( OR (HASONEVALUE('Gender Lookup'[Gender]), HASONEVALUE('Salesman Level'[Salesman Level])),
CumulativeSales,
SUMX(SalesTotal, [Cumulative Total])
)
However, I must me missing something here.
I have tried multiple different combinations of the HASONEVALUE parameters and still not getting the totals. They are either way off or just close.
Any ideas?
I have attached my new file.
Thanks
Testing.pbix (303.7 KB)
This is actually a bit harder around the totals.
I’ve had to work on it for a while and have come up with a solution.
There’s a little more to it than you would think.
The reason is because of the context that comes from the slicer. That really messes with the totals.
What you need to do is actually create a different table for the slicer like so
With no relationship to the model
Then with the correct formula you can get this
Reverse Cumulative =
VAR MonthFilter = ISFILTERED( 'Month of Year Filter'[Month Of Year] )
VAR FilteredMonth = SELECTEDVALUE( 'Month of Year Filter'[Month Of Year] )
VAR TotalCheck = HASONEVALUE( Dates[Month Of Year] )
VAR Cumulative = CALCULATE( [Number of Sales over time],
FILTER( ALL( Dates[Month Of Year] ),
Dates[Month Of Year] >= MIN( Dates[Month Of Year] ) ))
RETURN
IF( TotalCheck = FALSE(),
MAXX( SUMMARIZE( Dates, Dates[Month Of Year], "CumulativeTotal", Cumulative ), [CumulativeTotal] ),
IF( MonthFilter = FALSE(),
Cumulative,
IF( FilteredMonth = SELECTEDVALUE( Dates[Month Of Year] ), Cumulative, BLANK() ) ) )
So as you can see not as simple as initially.
Hopefully this works for you.
I’ve attached here also.
Testing Reverse Cumulative.pbix (300.0 KB)
Wow Sam
You are right. This totally changes the dynamic of the model.
Given that this testing file is only a subset of my final model it is quite a task!
I will see what I can do with it. Well done!
Hi Sam,
I have worked my way through this rather complicated formula and it is quite brilliant but has a bit of a shortcoming.
Firstly, the total is wrong. In this example, it should be 426, not 412.
The second thing is that I need to reference the result of this in other measures, but now since we have removed the month filter from the calculation, I am getting incorrect results.
As a rather crude example, say I need “Chosen Sales Values”
Chosen Sales values = [Reverse Cumulative]
If I put this in a table against sales levels I will only get the full total, not the total of the chosen levels as I do not have the correct filter context
See attached
Testing Reverse Cumulative.pbix (305.5 KB)
hmmm I’m confused around the totals
Your earlier example at the top of the post also had 412? Has the total always been wrong?
It’s look correct to me at the moment just by looking through the numbers
Same as here
I’m working on the other question to see if there’s a better solution for this one
Hi,
Yes. It has always been wrong. I had not noticed that earlier. A bit of an embarrassing oversight. I was nearly ready to present to client before I caught it. I had to pull back on that. As a sanity check I dumped the results into Excel to check them and noticed the error.
How about using a different formula for this other calculation.
I’ve done a bit of testing and this is probably the best way to go.
Here’s the formula
Selected Total =
CALCULATE(
CALCULATE( [Number of Sales over time],
FILTER( ALL( Dates[Month Of Year] ), Dates[Month Of Year] >= MIN( Dates[Month Of Year] ) )),
FILTER( ALL( Dates[Month Of Year] ), Dates[Month Of Year] = SELECTEDVALUE( 'Month of Year Filter'[Month Of Year] ) ) )
And calcs
Here’s an updated formula as well. I’ve attempted to simplify it.
Reverse Cumulative =
VAR MonthFilter = ISFILTERED( 'Month of Year Filter'[Month Of Year] )
VAR FilteredMonth = SELECTEDVALUE( 'Month of Year Filter'[Month Of Year] )
VAR TotalCheck = ISFILTERED( Dates[Month Of Year] )
VAR Cumulative = CALCULATE( [Number of Sales over time],
FILTER( ALL( Dates[Month Of Year] ), Dates[Month Of Year] >= MIN( Dates[Month Of Year] ) ))
VAR TotalsAbsolute = MAXX( SUMMARIZE( Dates, Dates[Month Of Year], "CumulativeTotal", Cumulative ), [CumulativeTotal] )
RETURN
IF( TotalCheck = FALSE(), TotalsAbsolute,
IF( MonthFilter = FALSE(), Cumulative,
IF( FilteredMonth = SELECTEDVALUE( Dates[Month Of Year] ), Cumulative, BLANK() ) ) )
Attached
Testing Reverse Cumulative.pbix (300.2 KB)
Thanks Sam
I will try these.
To assist you I attached the spreadsheet to show you what I am trying to achieve
Testing.xlsx (77.5 KB)
Power BI calculations are never wrong, so it has to be the data or some other filter causing this problem.
Can you isolate the issue?
It is probably something with the way that the number of sales are calculated in the first place.
The data is in the spreadsheet I attached.
If I look at the spreadsheet the numbers are slightly different for the grand total
Power BI
Just noticed that the AUG and DEC column are wrong in Power BI
Testing Reverse Cumulative.pbix (302.1 KB)
All I can say is it’s probably to do with the alignment of dates.
The months you are calculating in excel are probably not the same as in Power BI
Number of Sales over time =
// Calculate timeframe for leavers. Previous year backdated by number of years selected
VAR StartDate = DATE(YEAR(TODAY())-[Back Year Selection],1,1)
VAR EndDate = DATE(YEAR(TODAY())-1,12,31)
VAR DateRange =
FILTER(
ALL(Dates),
Dates[Date] >= StartDate
&& Dates[Date] <= EndDate
)
RETURN
CALCULATE(
DISTINCTCOUNT(Sales[Salesman ID]),
DateRange,
VALUES(Dates[Month Of Year]) // Bring the months back into the equation
)
Have you check they are looking at exactly the same date range as you have in this formula?
Power BI is just calculating up the data context it is given. So that’s really all that could be different in this case in my view.
Hi,
If I am right, then Power BI should be calculating from 2018-3, ie 1/1/2015 - 31/12/2017
Excel is filtering the years 2015, 2016, 2017
They are identical.
The actual matrix data is the same, just not the total lines. This is where the error is creeping in.
I agree that’s the date range.
Could it be that excel maybe is incorrect and Power BI is right.
I can’t see any reason why Power BI would be incorrect at the moment.
The reason the total could be different to the individual values in the matrix is because the total is looking at the entire month and saying (in July ) that across this one month over the past 3 years there has been 10 unique salespeople.
The amount within the matrix could be different because within each level there could be unique salespeople but they maybe are reference across multiple levels.
You seen here it actually seems to be the case.
This particular ID is reference across 4 different levels.
That’s why the total are different in Power BI. I would go as far to say Power BI is more technically correct here.
Attached
Testing Reverse Cumulative (2).pbix (301.9 KB)
