cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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

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

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.

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])))



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

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.



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

Hi @MattAllington,

I apologize.

I am so sorry if I hurt you somehow.

My English is not that good, so please forgive me if I got wrong anywhere in my posts.

And you are far far away from me in the dax concepts and Power BI, in fact you are an author of a book for DAX.

you are like a teacher to me. I just started Power BI Directly a month ago,No power Query, No Power Pivot and No Power View.

I am very poor in all DAX and Power Concepts.

Please accept my apology and help me out in my problem if you come across any solution.

 

Thank You.  

Hi @MattAllington @jahida and @v-micsh-msft

Here is the sample data and my Model.

DimItem

ItemKey

ItemNumber

Company Code

1

101

A

2

101

B

3

101

C

4

102

A

5

102

B

6

102

C

7

103

A

8

104

A

9

104

B

10

104

C

 

DimCompany

Companykey

CompanyCode

CompanyName

1

A

Com1

2

B

Com2

3

C

Com3

   

 

DimCustomer

Customerkey

Customer Code

CustomerName

1

11

Cus1

2

12

Cus2

3

13

Cus3

4

14

Cus4

5

15

Cus5

   

 

Fact1 (Grouped By ItemNumber from DimItem)

Query: SELECT I.ItemNumber, sum(F.Revenue) FROM Fact F inner join DimItem I on I.ItemKey=F.ItemKey group by I.ItemNumber

ItemNumber   Revenue

101                       600                    

102                       600

103                       300

Now the Following Formula for Contribution and running totals are working fine:

I put all these in a table vis:

ItemNumber     Revenue         RevenueContribution(DESC Order)          RunningTotal

TotalRevenue = CALCULATE(SUM(Fact[Revenue]),ALL(Fact))

RevenueContribution =  DIVIDE(SUM(Fact[Revenue]),[TotalRevenue],0)

RunningTotal= MAXX(Fact,CALCULATE([RevenueContribution],Fact[Revenue] >= EARLIER(Fact[Revenue]),ALL(Fact)))

 

Same formulaes with Following Fact:

 

Fact2

Query: SELECT I.ItemKey,I.ItemNumber,CO.CompanyKey,CS.CustomerKey,sum(F.Revenue)

FROM Fact F

inner join DimItem I on I.ItemKey=F.ItemKey

inner join DimCompany  CO on CO.CompanyKey=F.CompanyKey

inner join DimCustomer CS on CS.CustomerKey = F.CustomerKey

group by I.ItemKey,I.ItemNumber,CO.CompanyKey,CS.CustomerKey

ItemKey  ItemNumber    CompanyKey  CustomerKey  Revenue

    1                  101                       1                     1                     100                   

    2                  101                       2                     1                     200

    3                  101                       3                     2                     300

    4                  102                       1                     3                     400

    5                  102                       2                     1                     200            

Now the Following Formula for Running Totals are not working fine:

I put all these in a table vis:

ItemNumber     Revenue         RevenueContribution(DESC Order)          RunningTotal

TotalRevenue = CALCULATE(SUM(Fact[Revenue]),ALL(Fact))

RevenueContribution =  DIVIDE(SUM(Fact[Revenue]),[TotalRevenue],0)

RunningTotal= MAXX(Fact,CALCULATE([RevenueContribution],Fact[Revenue] >= EARLIER(Fact[Revenue]),ALL(Fact)))

 

I need second fact to build model and to filter reports according to the respective company and customer.

Means I want to take CompanyName from DimCompany and filter the report on it same with Customer.

Where am I lagging please help me guys.

 

Thanks in advance.

 

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

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.

 

 

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

 

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.

taumirza,

Apologize for the late response.

Response to your earlier posts,

RunninTotal(GSContribution) = MAXX(Fact,CALCULATE([GSContributio], Fact[GrossSales] >= EARLIEAR(Fact[GrossSales]),ALL(FACT)))

OR

RunninTotal(GSContribution) = CALCULATE([GSContribution],FILTER(Fact,Fact[GSCont​ribution] >= MINX(Fact,[GSContribution])))

OR

RunninTotal(GSContribution) = MAXX(SELECTCOLUMNS(Fact,"Contr",[GSContribution]),​CALCULATE([GSContribution],FILTER(Fact,[GSContribu​tion] >= [Contr])))

The first formula won't work, it will always return 100%, Second one should work, based on Descending order of [GSCont​ribution], for the 3rd one, could you please explain a bit for the [Contr] here?

By the way, could you please share about your data view under Power BI Desktop screenshot, with the table column information that described here? We may provide a more suitable solution based on the DataSet.

Regards

 

Hi @v-micsh-msft,

all of the three fomulas giving me 100% only. Leave about that. consider this:

I have,

DimItem(ItemKey,ItemNumber,CompanyKey) (One ItemNumber can have Multiple ItemKey because of CompanyKey in it)

FactRevenue(ItemKey,CompanyKey,CustomerKey,BrandKey,DateKey,Date,Revenue) (Fact is day level)

Fact and Dim Item Joined on ItemKey.

when i try to put ItemNumber from DimItem and Revenue from FactRevenue in a table visual with following Measures.

 

TotalRevenue = CALCULATE(SUM(FactRevenue[Revenue]),ALL(DimItem[ItemNumber]))

RContr =  DIVIDE(SUM(FactRevenue[Revenue]),[TotalRevenue],0)

1. RT = CALCULATE([RContr],FILTER(ALL(FactRevenue),FactRevenue[AccountingDate] <= MAX(FactRevenue[AccountingDate])),VALUES(DimItem[ItemNumber]))

 OR

2. RT= MAXX(FactRevenue,CALCULATE([RContr],FactRevenue[Revenue] >= EARLIER(FactRevenue[Revenue]),ALL(FactRevenue)))

I am not getting good results. Please consider the output

 

1. RT

:Capture1.PNG

 

2. RT

Capture2.PNG

 

Impactful Individual
Impactful Individual

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.

Hi @jahida,

I got this error when i use the formula you gave;

Error Message:

MdxScript(Model) (1, 199) Calculation error in measure 'PYQueryActual'[GS_RunningTotalPY]: Min or Max of booleans or strings are not currently supported.

Stack Trace:

 

Invocation Stack Trace:

 

Activity ID
23b334aa-8351-dda4-5704-77193dcda03e

Time
Mon Aug 15 2016 16:41:04 GMT+0530 (India Standard Time)

Version
2.37.4464.361 (PBIDesktop)

Error Code
rsDataShapeProcessingError

OData Error Message
Failed to execute the query.

 

Impactful Individual
Impactful Individual

Paste in the exact formula you tried..?

sorry @jahida,

i typed it wrong now there is no errors with formula but Running totals increased from 100% to 100.05% for every item in the Table.

here is what i did, as you said

First i chnged,

TotalGS = CALCULATE(SUM('Fact'[Gross Sales CY]),ALL(DimItem[ItemNumber]),ALLSELECTED(DimItem))

and then

RunningTotal = SUMX(FILTER(CALCULATETABLE(SUMMARIZE('Fact',DimItem[ItemNumber],"Contr",[GSContribution]),ALLSELECTED(DimItem[ItemNumber])),[Contr] >= MINX(VALUES(DimItem[ItemNumber]),[GSContribution])),[Contr])

 

Note: ItemNumber is different column and ItemKey is different column in DimItem

           I have Itemkey in both Fact and DimItem on which i have joined.

           ItemKey is Unique and ItemNumber can repeat 

                                                                                   DimItem Ex: ItemKey  ItemNumber  Company

                                                                                                              1                101                 A

                                                                                                              2                101                 B

                                                                                                              3                101                 C

Does this fact have any effect on the formula
Please help me. Try to look at the image i am getting 100.05% now

 

 

Capture.PNG

 

 

Anybody there...!!!Smiley Sad

Impactful Individual
Impactful Individual

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.

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.

 

 

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors