cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
taumirza Helper IV
Helper IV

Running Total

Hello All,

Please help me with this.

Revenue is a Default meausre from SQL server source.

I calulated Revenue Contribution(Measure) as DIVIDE(SUM(Query1[Revenue]),[Total Revenue],0)

then after sorting the Contribution I tried calculating another Measure as follows but earlier is not suppose to show 'Contribution' measure while i am typing

 

MAXX(Query1,CALCULATE([Contribution],[Contribution] >= EARLIER("I want Contribution Measure here"),ALL(Query1)))

for running total (the calculation i got from this post )

 

But this is possible with Revenue which is a default Measure from source.

MAXX(Query1,CALCULATE(SUM(Quer1[Revenue]),Quer1[Revenue] >= EARLIER(Query1[Revenue]),ALL(Query1)))

 

Is there any way to pass 'Contribution' as argument to 'Erliler' function

or Calculating Contribution at sequel level and bringing it as a default measure as revenue is only the way.

I dont want to  

 

Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions

Re: 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/



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

View solution in original post

20 REPLIES 20

Re: Running Total

Are you writing a measure or a calculated column?

What defines the order of the running total?  Do you have an ID column in your data table, or a date column or something?  

Do you have a calendar table?

How are you planning on visualising this?  In a Pivot table with some ID column on rows and the running total in values?  



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
taumirza Helper IV
Helper IV

Re: Running Total

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

Re: Running Total

=calculate([contribution],filter (all(table),table[item] <=max(table[item])))



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
taumirza Helper IV
Helper IV

Re: Running Total

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. 

Re: Running Total

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.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
taumirza Helper IV
Helper IV

Re: Running Total

no @MattAllington,

it didn't workout for my sample data.

please consider the following screenshot.

Capture.PNG

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.

 

 

taumirza Helper IV
Helper IV

Re: Running Total

no @MattAllington,

it didn't workout for my sample data.

please consider the following screenshot.

Capture.PNG

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.

 

 

jahida Impactful Individual
Impactful Individual

Re: Running Total

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.

Microsoft v-micsh-msft
Microsoft

Re: Running Total

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 Item​Number in ascending order.

If any further consideration or help needed, please feel free to post back.

Regards

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors