topic Re: Running total using sum with duplicate values in data in DAX Commands and Tips

Running total using sum with duplicate values in data

Jim_PBI — Mon, 04 Sep 2023 13:31:35 GMT

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

Re: Running total using sum with duplicate values in data

eliasayyy — Mon, 04 Sep 2023 14:51:00 GMT

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]
)

Re: Running total using sum with duplicate values in data

Anonymous — Mon, 02 Oct 2023 03:05:54 GMT

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.