5 Replies Latest reply on Mar 3, 2016 6:43 AM by Rgg Rowe

# Calculate distinct amount of a subset

Hello,

This seems a simple problem, but confused me a lot...

The original data is as right part, the formatted table in Tableau is as left part. I want to calculate Valid_Rate = amount(valid equal1) / amount(name) = 3 / 6 = 50%

It is easy to calculate name amount with COUNTD([name]), the question is: how to calculate valid amount? (the amount should be 3)

Very appropriate for any help.

formatted table
original data

 name valid other a 1 asdf b 0 df a 1 ghf a 1 werw a 1 sdf b 0 xcv c 0 tyu c 0 khjk d 1 iuo d 1 kjl e 0 nd e 0 werw f 1 xcx f 1 cfg

Thanks,

Rowe

• ###### 1. Re: Calculate distinct amount of a subset

Thanks Shawn for your reply. I just tried it but got 8 of SUM([Valid]), the expected result should be 3.

Is there any other method i could try? Thanks.

• ###### 2. Re: Calculate distinct amount of a subset

Hello Rowe,

You'll need to use an LoD (Assuming I understand the requirement correctly)

SUM({ FIXED [Name] : MIN([Valid]) }) / COUNTD([Name])

That will return a Single 1 or 0 For each NAME depending on if there is a Valid record for that name.

And the result of the Calc is 0.5

Hope that helps.

Regards,

Rody

• ###### 3. Re: Calculate distinct amount of a subset

So appreciate Rody ! The LoD solution works for me.

Thank you very much again.

• ###### 4. Re: Calculate distinct amount of a subset

No problem. Glad I could help.

If you run into any problems let me know.

Regards,

Rody

• ###### 5. Re: Calculate distinct amount of a subset

I will. Have a nice day