Forum Discussion
Cumulative Total
I am trying to create a visual which shows the cumulative total. I have tried creating a quick measure using running total, but this is the result I get:
My base value is the count, and the field is the month. It seems to be calculating backwards for some reason. I have tried amending the formula as well, but nothing I do seems to change the results.
This is the desired result:
Any suggestions as to what is wrong with my measure or what formula I can use to achieve this?
Hi Anonymous
If it's calculating backwards, you may need to check that your "Month" column is formatted as "Month Year". Once you do that, you can use a measure like below:
Measure = TOTALYTD ( SUM ( Table[Column] ) , Table[Date] )
This is the tabular view:
Hope this helps!
TheoHi Anonymous ,
I have created a simple smaple, please refer to it to see if it helps you.
Add an index in Power Query.
Create a measure.
Measure = CALCULATE(SUM('Table'[count]),FILTER(ALL('Table'),'Table'[Index]<=SELECTEDVALUE('Table'[Index])))Or you can use 'table'[date]<=selectedvalue('table'[date]) replace the index part.
If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 Replies
Hi Anonymous
If it's calculating backwards, you may need to check that your "Month" column is formatted as "Month Year". Once you do that, you can use a measure like below:
Measure = TOTALYTD ( SUM ( Table[Column] ) , Table[Date] )
This is the tabular view:
Hope this helps!
TheoAnonymous just in case you have complications with the above, another way of calculating cumulatives in this scenario is with the following measure:
Measure 2 = CALCULATE ( SUM ( 'Table'[Count] ) , 'Table'[Date] <= MIN ( 'Table'[Date] ) )Output is per below:Hope this helps!
Theo 🙂
Hello Theo,
Thank you for helping me out with this. However, I am still facing the same issue.
This is how I've amended the formula:
**bleep** New Cust = TOTALYTD ( COUNTA(Consolidated[New Customer]), Consolidated[Cust Join Date])My date column is formatted in month year format, but my results remain the same.
Also, if it matters, I am trying to count the number of new customers, and while SUM does not work, COUNTA seems to produce some form of result, even if not in the correct format. I'm still not very sure what is wrong my workings. I really appreciate you helping me through this.
Anonymous did you try the other option I provided?
I'd recommend changing your COUNT to a measure like Count Customers = COUNT ( Consolidated[New Customer] )
From there, try either:
1. Measure = TOTALYTD ( [Count Customers] ) , Table[Date] )
or
2. CALCULATE ( [Count Customers] ) , 'Table'[Date] <= MIN ( 'Table'[Date] ) )
Of those don't work, it might be best you provide some dummy data.
Best of luck!
Theo
Hi,
Try this one It will help to you Your scenarios,Thanks,
Thennarasu RHi Anonymous ,
I have created a simple smaple, please refer to it to see if it helps you.
Add an index in Power Query.
Create a measure.
Measure = CALCULATE(SUM('Table'[count]),FILTER(ALL('Table'),'Table'[Index]<=SELECTEDVALUE('Table'[Index])))Or you can use 'table'[date]<=selectedvalue('table'[date]) replace the index part.
If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy information.
Best Regards
Community Support Team _ Polly
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
