Running Total based on category

[Anonymous]

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	Manpower	150
A	Machine	80
B	Material	300
B	Manpower	140
B	Machine	120
C	Material	240
C	Manpower	200
C	Machine	10

 

Ideal Output:

Product	Cost	Cost Running Total
B	560	560
C	450	1010
A	430	1440

My current workaround is to use the following DAX formula to create a calculated table, 'Data by Product'; connect the two tables, 'Data' and 'Data by Product' with a relationship table, 'Product' that contains only { "A", "B", "C" }, then finally use the following measure, "Cost Running Total" to calculate the running total cost based on the [Cost] column in descending order. 

Data by Product = 
SUMMARIZE(
    'Data',
    Data[Product],
    "Cost", SUM(Data[Cost])
)

Cost Running Total = 
    CALCULATE(
        SUM(Data[Cost]),
        FILTER(
            ALLSELECTED('Product'[Product]),
            Data[Cost] >= MIN('Data by Product'[Cost])
        )
    )

Any less cumbersome way to perform the same calculation? 

[DataInsights · Accepted solution]

@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 · Accepted solution]

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)

[DataInsights · Accepted solution]

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