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

 

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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

:

 

2. RT