Forum Discussion
Running total using sum with duplicate values in data
I'm trying to calculate a running total using sum which I've already been able to do. The issue is that my data contains duplicates that need to be handled by taking an average/min before I can calculate the running total. The duplicates must remain in the data. Please could somebody share how I can do this with DAX?
An example of my dataset is here
| Date | Item | Sold |
| 04/09/2023 | Chair | 100 |
| 04/09/2023 | Chair | 100 |
| 04/09/2023 | Chair | 100 |
| 05/09/2023 | Chair | 150 |
| 05/09/2023 | Chair | 150 |
| 05/09/2023 | Chair | 150 |
| 06/09/2023 | Chair | 200 |
| 06/09/2023 | Chair | 200 |
| 06/09/2023 | Chair | 200 |
| 04/09/2023 | Sofa | 100 |
| 04/09/2023 | Sofa | 100 |
| 04/09/2023 | Sofa | 100 |
| 05/09/2023 | Sofa | 150 |
| 05/09/2023 | Sofa | 150 |
| 05/09/2023 | Sofa | 150 |
| 06/09/2023 | Sofa | 200 |
| 06/09/2023 | Sofa | 200 |
| 06/09/2023 | Sofa | 200 |
This is the expected result from the calculation
| Date | Item | Running total |
| 04/09/2023 | Chair | 100 |
| 05/09/2023 | Chair | 250 |
| 06/09/2023 | Chair | 450 |
| 04/09/2023 | Sofa | 100 |
| 05/09/2023 | Sofa | 250 |
| 06/09/2023 | Sofa | 450 |
Hi Jim_PBI ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a table.
New Table = SUMMARIZE( 'Table', 'Table'[Date], 'Table'[Item], "AvgSold", AVERAGE('Table'[Sold]) )(3)We can create measures.
Measure = VAR CurrentDate = MAX('New Table'[Date]) VAR CurrentItem = MAX('New Table'[Item]) RETURN SUMX( FILTER( ALL('New Table'), 'New Table'[Date] <= CurrentDate && 'New Table'[Item] = CurrentItem ), 'New Table'[AvgSold] )Running total = SUMX(SUMMARIZE('New Table',[Date],[Item],"total",[Measure]),[total])(4) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
hello Jim_PBI first get the unique value of date using max or average if all data are the same the average should return the same number
so UniqueDailyAverage = CALCULATE(AVERAGE('Table'[Sold]))
then use a sumx function
Running Total =
SUMX(
FILTER(
ALLSELECTED('Table'),
'Table'[Date] <= MAX('Table'[Date])
&& 'Table'[Item] = MAX('Table'[Item])
),
'Table'[UniqueDailyAverage]
)
OR
create a new calculate table using summarize to get unqiue values
AggregatedTable =
SUMMARIZE(
'OriginalTable',
'OriginalTable'[Date],
'OriginalTable'[Item],
"AvgSold", AVERAGE('OriginalTable'[Sold])
)
now just create emasureRunning Total =
VAR CurrentDate = MAX('AggregatedTable'[Date])
VAR CurrentItem = MAX('AggregatedTable'[Item])RETURN
SUMX(
FILTER(
'AggregatedTable',
'AggregatedTable'[Date] <= CurrentDate
&& 'AggregatedTable'[Item] = CurrentItem
),
'AggregatedTable'[AvgSold]
)Hi Jim_PBI ,
According to your description, here are my steps you can follow as a solution.
(1) My test data is the same as yours.
(2) We can create a table.
New Table = SUMMARIZE( 'Table', 'Table'[Date], 'Table'[Item], "AvgSold", AVERAGE('Table'[Sold]) )(3)We can create measures.
Measure = VAR CurrentDate = MAX('New Table'[Date]) VAR CurrentItem = MAX('New Table'[Item]) RETURN SUMX( FILTER( ALL('New Table'), 'New Table'[Date] <= CurrentDate && 'New Table'[Item] = CurrentItem ), 'New Table'[AvgSold] )Running total = SUMX(SUMMARIZE('New Table',[Date],[Item],"total",[Measure]),[total])(4) Then the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
