Sum Distinct Values in Table Total

[waleeed_mahmood]

Hello,

I have a question that i cant seem to solve.

I have the following table:

Region Coverage	Sales Employee	Targets
East	user1	1,350,000
East	user2	1,350,000
East	user3	1,350,000
East	user4	1,350,000
Total		5,400,000

Since, the region is the same and the targets are the same, can we have the total to be 1,350,000 since all targets in the regions are the same?

Thank you in advance.

Waleed

[Thiago_Justen_]

Try this out: Sum(Aggr(Sum(Targets),[Region Coverage]))

Or

Sum(total <[Region Coverage]> Targets)

[waleeed_mahmood]

Hi Thiago,

It doesnt work. It gives 5400000 to one user and makes rest 0. Also, i do have other regions in this which have targets of their own and not same at all.

Thanks

[Thiago_Justen_]

Check: Sum(Aggr(Sum(Targets),distinct [Region Coverage]))

[waleeed_mahmood]

Hello,

This turns it all 0. 

Thanks

[Thiago_Justen_]

Are you able to share a mockup of your data?

[waleeed_mahmood]

Hello thiago,

Unfortunately, the data is confidential and in a vast amount. all other regions are okay. But East we have it set up differently and we need to add it only once.

This is the following table with all users:

Region Coverage	Users	Target
Total		7,300,000
Central	Total	1,250,000
User1	400,000
User2	25,000
User3	75,000
User4	300,000
User5	200,000
User6	0
User7	250,000
East	Total	5,400,000
User8	1,350,000
User9	1,350,000
User10	1,350,000
User11	1,350,000
West	Total	650,000
User12	350,000
User13	100,000
User14	200,000

Thanks.

[Jflori4n]

Hi Thiago,

Try with -> sum({<Users={'Total'}>} Target)

However, I recommended normalized your data

[Thiago_Justen_]

Here it is:

Sum(total <[Region Coverage]> distinct Targets)

The output:

[waleeed_mahmood]

Hi Thiago,

Is it possible you could try this with the larger data set i provided above? I have tried the solution above and it is giving me random numbers again. Please note that i have other conditions in my set analysis as well. Also, Shouldnt the set analysis have {} around the <> symbols? so it becomes 

sum(total {<[region Coverage]>} distinct Targets)

Please let me know.

Thank you