cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## 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
1 ACCEPTED SOLUTION
Community Support

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 2
Community Support

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.

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.