Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Running Total based on category

With the following table 'Data', I would like to write a measure that calculates the running total based on "Product".   Input Table: 'Data' Product Type Cost A Material 200 A Manpow...
  • DataInsights's avatar
    5 years ago

    Anonymous, try this:

     

    1. Create a table ProductMaster. Sort the Product column by the Index column (controls the sorting in the table visual). 

     

     

    2. Join ProductMaster to the data table.

     

     

    3. Create measures.

     

    Total Cost = SUM ( ProductCost[Cost] )
    
    Running Total = 
    VAR vSelProd =
        SELECTEDVALUE ( ProductMaster[Index] )
    VAR vResult =
        CALCULATE (
            [Total Cost],
            ProductMaster[Index] <= vSelProd,
            ALL ( ProductMaster[Product] )
        )
    RETURN
        vResult

     

    4. Create a table visual, using ProductMaster[Product] and the measures.

     

    5. Result:

     

     

  • FrankAT's avatar
    5 years ago

    Hi Anonymous 

    what about the following solution:

     

     

    Sum of Cost = SUM(Data[Cost])
    
    Running Total = 
    CALCULATE(
        [Sum of Cost],
        FILTER(
            ALL(Data),
            MIN(Data[Product]) <= Data[Product]
        )
    )

     

    With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
    FrankAT (Proud to be a Datanaut)