Sum distinct

Izham

Hi, i have dataset of products that have repetition item code and quantity, how do i sum the quantity?

ggenovese

Using the example data you provided I am getting a deduped SUM of 1,600 using the beast mode I provided earlier, if I add a total row it also evaluates to 1,600. Not sure why it isn't working for you.

Going back to my earlier question, I'm curious what your DAX calculation would evaluate to if you were to add a Product D that has a quantity of 300, 500, or 800?

ggenovese

Hi - I shared a beast mode that you can use in a card, not an ETL

ggenovese

There are a number of ways to handle this, but the easiest might be to simply use MAX or AVG rather than SUM

ColemenWilson

Using fixed functions! https://domo-support.domo.com/s/article/4408174643607?language=en_US

GrantSmith

SUM(`quantity` / SUM(1) FIXED (BY `product_id`))

Use a fixed function to determing the number of records you have for each product, divide the quantity by that to get fractional quantity and then SUM it all together to get the overall quantity.

Izham

Thank you for the feedback. However, when I compare it to an excel file, I still do not get the same answer.

For the record, the duplication occurs annually. Please help.

ggenovese

Assuming your data looks kind of like this, where the same product qty is repeated within a year

then this beast mode will dedupe and sum by product and year:

SUM(MAX(`qty`) FIXED (by `product`,`year`))

Izham

I am still receiving different quantities, the correct amount shown in the Excel picture above...

ggenovese

Hi - when an item code is repeated, is the qty always the same corresponding value?

Izham

Yes, but it can be different items as well…😓

Izham

in excel I'm using this DAX code :

SUMX(DISTINCT('Master Data'[LR_YR_art]),CALCULATE(DISTINCT('Master Data'[Soh CDC Current QTY])))

ggenovese

The DISTINCT expression in your DAX code is going to return a deduped list of qty values correct? Let's say for example, two items A & B both have a qty of 5, wouldn't the DAX code give you a total of 5 rather than 10? I don't know DAX so I want to make sure I'm interpreting that correctly.

Izham

Two distinct items will be used to calculate both items A and B. It will, of course, display two distinct totals.

Here is the example (in simple excel) :

Year	Product	Quantity
2022	a	300
2022	a	300
2022	b	500
2022	c	800
2022	a	300
Total		2200
Total year 2022	: 1600	not 2200 because item "a" is repeating 3 times

ggenovese

Using the example data you provided I am getting a deduped SUM of 1,600 using the beast mode I provided earlier, if I add a total row it also evaluates to 1,600. Not sure why it isn't working for you.

Going back to my earlier question, I'm curious what your DAX calculation would evaluate to if you were to add a Product D that has a quantity of 300, 500, or 800?

Izham

Is this in beast mode or in ETL?

ggenovese

Hi - I shared a beast mode that you can use in a card, not an ETL

Izham

Ok. Done it. Great!. Thank you very much yeahhhh 😁