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

20 REPLIES 20
Highlighted

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.

Highlighted
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.

Highlighted

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.

 

Highlighted
Impactful Individual
Impactful Individual

Paste in the exact formula you tried..?

Highlighted

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

 

 

Highlighted

Anybody there...!!!Smiley Sad

Highlighted

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

 

Highlighted

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

 

Highlighted

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.  

Highlighted

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.

 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors