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

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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.