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