DISTINCTCOUNT and SUM HEADACHE

[nikkollai]

Hello,

My dataset contains multiple rows with the same product name (component for a different product) I am trying to create a measure that would allow me to see on hand stock for each distinct product
You can see below in ONHAND field, quantities repeat for each relative product i need to see them as DISTINCT data. (DEMAND is aggregate / summed up data this one is easy)



Here is the link for the spreadsheet.

FINAL RESULT I AM BREAKING MY HEAD OVER

[TABLE="width: 335"]
<tbody>[TR]
[TD]PRODUCT[/TD]
[TD]DEMAND[/TD]
[TD]ONHAND
[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD]4677[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD]5548[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD]6649[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]DDD[/TD]
[TD]7364[/TD]
[TD="align: right"]646[/TD]
[/TR]
</tbody>[/TABLE]


DATA SET

[TABLE="width: 359"]
<tbody>[TR]
[TD]PRODUCT[/TD]
[TD]DEMAND[/TD]
[TD]ONHAND[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]975[/TD]
[TD="align: right"]54
[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]1480[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD="align: right"]1674[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]DDD[/TD]
[TD="align: right"]1250[/TD]
[TD="align: right"]646[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]1140[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD="align: right"]1636[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]DDD[/TD]
[TD="align: right"]1243[/TD]
[TD="align: right"]646[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]1511[/TD]
[TD="align: right"]54[/TD]
[/TR]
[TR]
[TD]BBB[/TD]
[TD="align: right"]923[/TD]
[TD="align: right"]24[/TD]
[/TR]
[TR]
[TD]CCC[/TD]
[TD="align: right"]1254[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]AAA[/TD]
[TD="align: right"]1478[/TD]
[TD="align: right"]54[/TD]
[/TR]
</tbody>[/TABLE]



Tks, Nick

 

[ndsutherland]

try average instead of sum

 

[nikkollai]

Wow!!! So simple It worked. THANK YOU

However, if anyone knows another way using functions DISTINCTCOUNT or ect. your ideas will be nice to see.

Tks again to ndsutherland

 

[TimRodman]

MAX would probably work too. Ideally, you would separate ONHAND data into a separate lookup table that has PRODUCT and ONHAND in it with only one row per PRODUCT.