Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

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 so...
Need Help
Anonymous's avatar
Anonymous
Not applicable
10 years ago

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.

v-micsh-msft's avatar
v-micsh-msft
Microsoft Employee
10 years ago

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