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/
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.
Can't test because I don't know exactly what your data looks like, but here's my idea of a solution:
Running Total = MAXX(SELECTCOLUMNS(Query1, "Contr", [Contribution]), CALCULATE([Contribution], [Contribution] >= [Contr]))
or
Running Total2 = CALCULATE([Contribution], FILTER(Query1, [Contribution] >= MINX(Query1, [Contribution]))
Not sure if either of those will work but both probably worth a try. If not, you might want to take a screenshot of your back-end table so we can get an idea of exactly what your data looks like.
Hi Taumirza,
What is your current situation?
Based on what I know, Earlier function only accept A column or expression that resolves to a column to be the first parameter.
So generally measure can’t be used here, at least for the measure defined in Power BI.
For those measures defined under Data source side, that measure is evaluated when getting data from the data source, actually those measures should be treated as calculated column in Power BI.
To calculate the running total, we should either create an Index to help calculate, or take use of the earlier function with a calculated column.
Actually, both of your measures (1 and 2) works. In order to have the RT and the Running Total to have the same result, we need to sort the ItemNumber in ascending order.
If any further consideration or help needed, please feel free to post back.
Regards
Hi jahida, MattAllington and v-micsh-msft
My Problem is: Getting Running Totals as 100% For All Three Calculations
What did i miss?
I have Fact with 'Itemkey',Yearkey,GrossSales columns, joined with 'DimItem' table on 'Itemkey'.
One ItemNumber in DimItem can have one or more ItemKey.
Fact is Grouped on Itemkey. Now
TotalGS = CALCULATE(SUM(GrossSale6s),ALL(DimItem[ItemNumber]))
GSContribution = DIVIDE(sum(GrossSales),[TotalGS],0)
RunninTotal(GSContribution) = MAXX(Fact,CALCULATE([GSContributio], Fact[GrossSales] >= EARLIEAR(Fact[GrossSales]),ALL(FACT)))
OR
RunninTotal(GSContribution) = CALCULATE([GSContribution],FILTER(Fact,Fact[GSContribution] >= MINX(Fact,[GSContribution])))
OR
RunninTotal(GSContribution) = MAXX(SELECTCOLUMNS(Fact,"Contr",[GSContribution]),CALCULATE([GSContribution],FILTER(Fact,[GSContribution] >= [Contr])))
Table viz:
ItemNumber(DimItem) GrossSales GSContribution RunningTotal(GSContribution)
1 500 45.45% 100%
2 300 27.27% 100%
3 200 18.18% 100%
N 100 9.09% 100%
Total 1100 100% 100%
Please help me with this.
I am very new to dax and Power BI.
I think all of those formulas assume that each line in the table corresponds to one line in the dataset, which in retrospect probably is unreasonable. I made a new formula that doesn't assume one entry per item number. I followed your naming for the table names, but not all the column names.
First a small change to TotalGS:
TotalGS = CALCULATE(SUM(Fact[Gross Sales]), ALL('DimItem'[Item]), ALLSELECTED(DimItem))
Then the main measure:
Run = SUMX(FILTER(CALCULATETABLE(SUMMARIZE('Fact', 'DimItem'[Item], "Contr", [GSContribution]), ALLSELECTED('DimItem'[Item])), [Contr] >= MINX(VALUES('DimItem'[Item]), [GSContribution])), [Contr])
If that doesn't work, please post a screenshot of the back-end data (top couple rows of the data table) so that we can know exactly what a solution would look like.
