Distinct sum

[RajaRajput28]

Hi all ,

I am working on building an application which has lot of Data. So what I am trying to achieve is that I need to show up the sum  of distinct  revenue and sales. Here the catch is that in revenue field , we also have some zero values. With it, we need to have distinct based upon the Id. When I am trying to get up the sum based upon Unique ID , I am not getting expected result. Because in unique ID , for couple of id's we have same revenue. I have added a table , So here I am trying to get a sum with ==> Sum (distinct{<ID = {'1','2','3','4','5'}>} (Revenue)) . It's giving me a sum of 1.8k but what's expected is 2.3 k. Can you guys please help me out with this ?? 

Qlik Sense Desktop , @tresesco @sunny_talwar 

This is a demo data for reference. 

ID	Item	Sales	Revenue
1	a	100	500
2	b	200	0
3	c	0	600
4	d	200	500
5	e	400	700
1	a	100	500
2	b	200	0
3	c	0	600
4	d	200	500
5	e	400	700
1	a	100	500
2	b	200	0
3	c	0	600
4	d	200	500
5	e	400	700
1	a	100	500
2	b	200	0
3	c	0	600
4	d	200	500
5	e	400	700
1	a	100	500
2	b	200	0
3	c	0	600
4	d	200	500
5	e	400	700

[tresesco · Accepted solution]

Try:

Sum (Aggr(Sum(distinct{<ID = {'1','2','3','4','5'}>} Revenue), ID))

[RajaRajput28]

That was real Quick , Thanks Tresesco for prompt response