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.
Hello
I imagine there will be a table (or chart) presenting Sales by period (as cumulative sum).
I'd like to calculate a cumulative for all people, but also when you select a person from the slicer the cumulative will be just for this person.
There are 15 periods in total and below is just some dummy data for three periods.
As you've seen, some people have multiple rows with figures.
I dont know where to start and will be grateful for your help 🙂
Ewa
Period Person Sales
1 Jane 10
1 Jane 11
1 John 13
1 Tom 5
1 Tom 19
2 Jane 18
2 John 5
2 Tom 20
2 Tom 3
2 Jane 1
3 John 5
3 Jane 11
3 Tom 7
3 Jane 6
3 Tom 11
Solved! Go to Solution.
Ah, check out the pattern here:
http://www.daxpatterns.com/cumulative-total/
Create a measure that is basically:
Measure = SUM([Sales])
Measures are context aware, meaning that if you display this measure in the context of "all" you will get the total of everyone but if you select a person from a slicer you would get just that one person. Note that you should be able to use the default "sum" aggregation and really not have to create the measure at all for something as simple of a calculation as this.
Thank you
What about Running Totals?
Sorry, I might have not been clear, but I am looking into running totals, I thought Running Totals are the same as Cumulative, but I realise that Cumulative 🙂
So Period 1 will show Period 1 totals, Period 2 will show Period 1 plus Period 2, Period 3 will show Period 1+2+3.
Thanks
Ewa
Some aspects of your post are unclear. But for a running sum you will definitely need an Index column. You add that via the Query Editor where there is an Add Column tab.
There could be an overall running sum - or - a running sum by Period which would reset at the start of a new period. But then you are asking for i.e. Period 3 to include Period 1 & 2 totals - so that isn't clear as to which you actually seek.
Another approach is your Period in Visuals you can set them up to be a hierarchy and you might experiment with that without any custom DAX needed.
Ah, check out the pattern here:
http://www.daxpatterns.com/cumulative-total/
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 |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |