Avoid counting duplicate values in straight table

[sangeess21]

Hi all,

I have a straight table where it shows duplicate values. I would like to count the ordered quantity only once for each order. I used expression sum(Ordered Quantity) for Ordered Quantity and it is counting the duplicate values too.

In the below example, order 15562 has three distinct block codes and hence shows the ordered quantity thrice. So, when I use the expression sum([Ordered Quantity]) it shows sum as 3 whereas we have only one ordered quantity for that order. How can I make the Ordered quantity to show 1 for the below order. Any ideas?

Thanks!

[Anil_Babu_Samineni]

May be this?

Sum(Aggr(sum([Ordered Quantity]), [Order Number]))

[sunny_talwar · Accepted solution]

More like this

Sum(Aggr([Ordered Quantity], [Order Number]))

or

Sum(Aggr(Only([Ordered Quantity]), [Order Number]))

or

Sum(Aggr(Sum(DISTINCT [Ordered Quantity]), [Order Number]))

or

Sum(Aggr(Avg([Ordered Quantity]), [Order Number]))

[sangeess21]

Thanks to both sides of you. Sunny's expression did the trick for me

[effinty2112]

Hi Sangeetha,

If you still want to show your value in a straight table then maybe:

Order Date	Order Number	Order Line	Block Code	Sum(Aggr(only([Ordered Quantity]), [Order Number],[Order Line]))
				3
09/05/2017	15592	190	MISC	1
09/05/2017	15592	190	PRICE	0
09/05/2017	15592	190	SHIP TO	0
09/05/2017	15592	191	MISC	1
09/05/2017	15592	191	PRICE	0
09/05/2017	15592	191	SHIP TO	0
09/05/2017	15593	190	MISC	1
09/05/2017	15593	190	PRICE	0
09/05/2017	15593	190	SHIP TO	0

I added a line to your order and a new order too to show the effect of more data.

Regards

Andrew

[its_anandrjs]

You have different order dates and different Block code also for the Quantity so it is shown 1 and total should be 3  for each quantity where else try some of this

Sum(Aggr(Sum(DISTINCT [Ordered Quantity]), [Order Number]))

Or

Count(Aggr(Sum(DISTINCT [Ordered Quantity]), [Order Number]))

[edmondo_tassi]

I dont sure to understand, have you think to use also dimensionality() function as possibility to differentiate total in different lier of data?