Forum Discussion
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 | 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?
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 vResult4. Create a table visual, using ProductMaster[Product] and the measures.
5. Result:
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)
2 Replies
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 vResult4. Create a table visual, using ProductMaster[Product] and the measures.
5. Result:
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)