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
MattAllington's avatar
MattAllington
Community Champion
10 years ago

=calculate([contribution],filter (all(table),table[item] <=max(table[item])))

Anonymous's avatar
Anonymous
Not applicable
10 years ago

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. 

  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      no MattAllington,

      it didn't workout for my sample data.

      please consider the following screenshot.

      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.

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      no MattAllington,

      it didn't workout for my sample data.

      please consider the following screenshot.

      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.

       

       

      • jahida's avatar
        jahida
        Impactful Individual
        10 years ago

        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.

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