cancel
Showing results for
Did you mean:  Helper IV

## Running Total

Hello All,

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

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.
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.  Helper IV

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.  Helper IV

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.  Helper IV

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.  Helper IV

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. 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.  Helper IV

it didn't workout for my sample data. 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.  Microsoft

Hi Taumirza,

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  Helper IV

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%

I am very new to dax and Power BI.  Microsoft

taumirza,

Apologize for the late response.

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  Helper IV

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

: 2. RT   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.  Helper IV

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

Paste in the exact formula you tried..?  Helper IV

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   Helper IV

Anybody there...!!!   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.  Helper IV

it didn't workout for my sample data. 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. Announcements #### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world. #### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st! #### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison! #### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better. Top Solution Authors
Top Kudoed Authors
Users online (2,897)