count distinct function in pivot table

[venkatesh61524]

Dear All,

I have a two columns data serial number and delivery number. Now if we choose one serial number have two same delivery numbers and different delivery number.

Example data want to see in Pivot table:(Requirement)

Serial No	Delivery Number	Total
51	801	2
	801
51	815	1
60	950	2
	950
60	990	1
80	1500	2
	1500
80	2000	1

The above example table .if we select 51 serial number can see three delivery numbers. But in three delivery numbers have two are same one is different for same serial number.now we need to show this data into pivot chart.

i have tried with this expression in pivot chart:

Aggr( Count(Distinct [Delivery Number]),[Serial No],[Delivery Number])

After expression have data in pivot table:

Serial No	Delivery Number	Total
51	801	1
	815	1
60	950	1
	990	1
80	1500	1
	2000	1

Please suggest and show the correct expression for my requirement ..

[MK_QSL]

This was a simple problem and you would have got answer within 10 minutes if while posting question, you would have supplied sample application or sample excel file with the result you was  looking for..

Happy Qliking !