I’m going to go through an interesting scenario that you’ll probably land on when you’re trying to run a Cumulative Total inside of Power BI. You may watch the full video of this tutorial at the bottom of this blog.
A member from the Enterprise DNA support forum posted this query. The member wanted to calculate a running balance like a Cumulative Total. The problem was every time a selection was made, the Cumulative Total recalculated. The member didn’t want that to happen.
In this case, the ALLEXCEPT is the perfect function to use. This scenario is actually a good review of how Cumulative Totals and the ALLEXCEPT function work together inside Power BI.
The Sample Data
Let’s start with the data given in the member’s example.
Basically, it shows the running balance on the table. The running balance is also shown on the visualization.
Now when you drag the Week Number across the slicer, you’ll see that the data rebalances as well.
So let’s say we want the data from Week 5.
What we want is for that Running Balance for Week 5 to remain at 635 instead of turning to 78 when the slicer is moved, as shown below.
This is happening because the Running Balance always starts from the first number on top. So when the slicer was moved, the number on top also changed. This affected the entire Running Balance.
Using The ALLEXCEPT Function
Normally, you’ll see ALLSELECTED being used in scenarios like this. But the ALLSELECTED function is only helpful when you’re just trying to show a dynamic Cumulative Total based on any date selection you’re making.
This is where ALLEXCEPT comes in.
In this particular case, we don’t want a dynamic total. The requirement is to prevent the total from recalculating every time a new date selection is made. This is what ALLEXCEPT does.
What we want to do is get a Cumulative Total while retaining the Week Number context. By using ALLEXCEPT, we’re still working through the selected dates on the slicer. But as far as the Running Balance is concerned, it will always go and look back to the very first week.
Looking at the visualization below, you’ll see that it also retains the correct value. Again, this is because the context now allows you to look back to the first result from Week 1.
***** Related Links *****
DAX Calculations: Total Of Average Results
Compare Actual Results to Budgets Using Power BI Forecasting
Calculating Reverse Cumulative or Reverse Running Total In Power BI
Conclusion
Cases like these may be unique because we want dynamic totals most of the time. But if you find yourself in situations where you don’t want the numbers to recalibrate with each selection, this technique is the perfect solution.
Also, the way we used the ALLEXCEPT function here is a perfect example of how you can remove context for some parameters, but retain context for the others within the same report pages.
Hopefully, you can find similar uses for ALLEXCEPT as you work with Power BI.
All the best
Sam
[youtube https://youtu.be/T2BsneMKOeU?rel=0&w=784&h=441]
