topic Re: Sum a column with duplicates in DAX Commands and Tips

Sum a column with duplicates

roberts_stats — Fri, 14 Oct 2022 12:30:03 GMT

How many apples are there at each Farm (with DAX)

Farm	Barrel	Apples	Taster	Vote
North Farm	Green Barrel	150	John	Sweet
North Farm	Green Barrel	150	Sue	Sweet
North Farm	Red Barrel	150	John	Bitter
South Farm	Blue Barrel	200	John	Sweet
South Farm	Blue Barrel	200	Sue	Sweet
South Farm	Pink Barrel	300	John	Sweet
South Farm	Pink Barrel	300	Sue	Bitter
South Farm	Pink Barrel	300	Carol	Sweet
South Farm	Red Barrel	50	Carol	Bitter

We want to create a measure which would sum the quantity of any Farm or any Barrel?

Like the Apple Measure shown below for example ...

Farm,Barrel,Apples,Taster,Vote
North Farm,Green Barrel,150,John,Sweet
North Farm,Green Barrel,150,Sue,Sweet
North Farm,Red Barrel,150,John,Bitter
South Farm,Blue Barrel,200,John,Sweet
South Farm,Blue Barrel,200,Sue,Sweet
South Farm,Pink Barrel,300,John, Sweet
South Farm,Pink Barrel,300,Sue,Bitter
South Farm,Pink Barrel,300,Carol,Sweet
South Farm,Red Barrel,50,Carol,Bitter

Re: Sum a column with duplicates

ValtteriN — Fri, 14 Oct 2022 09:55:00 GMT

Hi,

I am going to give a few pointers which should help you to solve this issue:

You can use CALCULATE to modify filter context of a measure/function by using functions such as ALL, REMOVEFILTERS, ALLEXCEPT. E.g. ALL(Table[column]) will remove filters affecting that column.

The use case you are describing is common and I recommend checking these resources to understand more:

Introducing CALCULATE in DAX - SQLBI
CALCULATE function (DAX) - DAX | Microsoft Learn
Filter context in DAX - SQLBI

I believe you can create your measure after reading these.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Sum a column with duplicates

roberts_stats — Fri, 14 Oct 2022 12:29:39 GMT

Hi ValtteriN

Thanks your answer, I'm going to edit and clarify my question a little because I don't believe those links resolve this question necessarily.

We want to create a measure which would sum the quantity of any Farm or any Barrel, not just filtering the sum to find one Barrel or one Farm.

Re: Sum a column with duplicates

v-easonf-msft — Thu, 20 Oct 2022 09:51:15 GMT

Hi, roberts_stats

You can consider add a summarized table to remove the duplicates records and keep the valid record columns.

Calculated Table:

Table 2 = SUMMARIZE('Table','Table'[Farm],'Table'[Barrel],'Table'[Apples])

Then you can perform calculations based on this summary table.

From the summary table you can easily tell how many apples are there at each Farm.

Best Regards,
Community Support Team _ Eason