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.

jahida's avatar
jahida
Impactful Individual
10 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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.

     

  • jahida's avatar
    jahida
    Impactful Individual
    10 years ago

    Paste in the exact formula you tried..?

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    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

     

     

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Anybody there...!!!:smileysad: