5 Replies Latest reply on Sep 30, 2013 8:20 PM by Ravi Darasi

# Distinct Count based on the Total Revenue

i have to calculate distinct count for members whose individual revenue should be >=0.1

Filters are country ,Member type , Gender

Here my objective is based on the filter, i have to get the count of distinct Active members. But when i check the count it's wrong.

Eg.: i am showing the dashboard like below

Note

Active Members : Total Revenue(Member) Should be >=0.1

• ###### 1. Re: Distinct Count based on the Total Revenue

Do  you mean the individual Member's % of Total Revenues should be greater than 10%?

• ###### 2. Re: Distinct Count based on the Total Revenue

Nope, If you see the Sample Excel Data.

Suppose same member has different transactions like Purchase & Returns. When we look on the total revenue for that member should be greater than "0". Some cases the total revenue will be <0.

• ###### 3. Re: Distinct Count based on the Total Revenue

Turn your data into a packaged workbook and I'll be happy to take a look. Posting an Excel pivot table really doesn't help us help you, especially when in your pivot table all Grand Totals are greater than zero. Thanks,

--Shawn

• ###### 4. Re: Distinct Count based on the Total Revenue

Ravi, To get your desired output you have to use Countd function instead of count.

COUNTD(expression)

Returns the number of distinct items in a group. Null values are not counted. This function is not available if you are connected to MS Excel, MS Access, or a text file. You can extract your data into an extract file to gain this functionality

Once you created the data extract please use the below calculation.

if Sum([Revenue])>=0.1 then countd([Member]) else 0 End

• ###### 5. Re: Distinct Count based on the Total Revenue

Hi,

Thanks for the information, i already used that way. But i couldn't  achieve it. My object is to get the Active partners(total revenue should be >0 and distinct count)

I have attached my samples (sample_v1 package, TableauData_V2 excel file) .

You can see there is Active Mbrs sheet in the Tableau package, i have filter by "Mgr4", but couldn't see any active members. actually it should display 1 active member but it doesn't display. it consider total revenue for that member not instead of apply the filter of Manager filter.

eg: total revenue for m1 has -250. So we cann't consider this Mbr is active Memberr.

If you filtered by manager " Mgr4", the total revenue for the same member is 350 . So it should consider as active members.

We have different filters like this.

Regards,

D.Ravi