Forum Discussion
Running Total
I wrote a blog post today about how to create a Pareto Cumulative Running Total
http://exceleratorbi.com.au/cumulative-running-total-based-on-highest-value/
Everything is a Measure.
And Revenue is a Default measure from Fact.
I want output like this.
After descending order of Contribution I will calculate Running Total.
Item Revenue Contribution RunningTotal
1 300 0.50 0.50
2 200 0.33 0.83
3 100 0.16 0.99
=calculate([contribution],filter (all(table),table[item] <=max(table[item])))
Negative MattAllington..!!
how can we use '<= max(item)' condition, when Items are sorted in desccending order of contribution...??
what if I dont have Item Number and want to use Item Name from dimension, this max() function shouldn't work with strings as far as i know.
I just provided you a solution that works for the sample data you posted. If your data is not like the sample data you posted, then it won't work - of course.
no MattAllington,
it didn't workout for my sample data.
please consider the following screenshot.
All Measures
Here
1. Contr = DIVIDE(SUM(Query1[Revenue]),[Total Revenue],0)
where Total Revenue = CALCULATE(SUM(Query1[Revenue]),ALL(Query1))
and the formula you suggested
RT = CALCULATE([Contr],FILTER(ALL(Query1),Query1[ItemNumber] <= MAX(Query1[ItemNumber])))
2.. Contribution = SQL generated (Revenue / sum(Revenue) ) from SQL Source
Running Total = MAXX(Query1,CALCULATE(SUM(Query1[Contribution]),Query1[Contribution] >= EARLIER(Query1[Contribution]),ALL(Query1)))
in which 2 is working fine .But i dont want to calculate Contribution at SQL level.