Running total using sum with duplicate values in data

[Jim_PBI]

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

[Anonymous · Accepted solution]

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. 

[eliasayyy]

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 emasure

Running Total =
VAR CurrentDate = MAX('AggregatedTable'[Date])
VAR CurrentItem = MAX('AggregatedTable'[Item])

RETURN
SUMX(
FILTER(
'AggregatedTable',
'AggregatedTable'[Date] <= CurrentDate
&& 'AggregatedTable'[Item] = CurrentItem
),
'AggregatedTable'[AvgSold]
)