Announcements
cancel
Showing results for
Did you mean:
Creator

## Sum Distinct Values in Table Total

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?

Waleed

23 Replies

This is the output I have:

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
Creator
Author

Hi,

Do you mind sharing the QVF file?

Thank you

Here is the script I've used:

Temp:
Region Coverage, Users, Target
Central,User1, 400000
Central,User2, 25000
Central,User3, 75000
Central,User4, 300000
Central,User5, 200000
Central,User6, 0
Central,User7, 250000
East,User8, 1350000
East,User9, 1350000
East,User10, 1350000
East,User11, 1350000
West,User12, 350000
West,User13, 100000
West,User14, 200000
];

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
MVP

You might be able to use this

Aggr(NODISTINCT Sum(DISTINCT Target),  [Region Coverage]))

Great @sunny_talwar!!

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
Creator
Author

Hello Sunny, Thiago,

Using your script and expressions, this is what i get.

Except East, other regions have their own targets that should appear. Only east is the exception.

Thank you.

MVP

I am not entirely sure what you need, but may be this

If([Region Coverage] = 'East',
Aggr(NODISTINCT Sum(DISTINCT Target),  [Region Coverage])),
Sum(Target)
)

Creator
Author

Hi Sunny,

Thank you

If(Dimensionality()=0,Sum(Targets),Sum(total <[Region Coverage]> distinct Targets))

Thiago Justen Teixeira Gonçalves
Farol BI
WhatsApp: 24 98152-1675
Skype: justen.thiago
MVP

I am guessing Current Result uses Sum(Target)... if it does, then I think this should work

If([Region Coverage] = 'East',
Aggr(NODISTINCT Sum(DISTINCT Target),  [Region Coverage])),
Sum(Target)
)