10 Replies Latest reply on May 21, 2019 11:34 AM by Tyler Garrett

# count distinct id percent of total more than 100

I am doing a pie chart to represent the percentages of distinct ids in two categories. i am doing table calculation across table. it is giving more than 100% results.

how to do this.

when i do  count its working. but that's not what i am looking for.

• ###### 1. Re: count distinct id percent of total more than 100

Hi, Deepika

Can you provide a sample workbook?

ZZ

• ###### 2. Re: count distinct id percent of total more than 100

Deepika,

Can you share the workbook.

• ###### 3. Re: count distinct id percent of total more than 100
1 of 1 people found this helpful
• ###### 4. Re: count distinct id percent of total more than 100

Hi Deepika,

You can resolve this by using an LOD, create a calculated field-> "Dist Id`s" ->

{ FIXED [Category]:COUNTD([Order ID])}

and then drag it and apply the table calculation over it, you will get values rounding up-to 100% , you can then create a pie out of that!

Hope this helps.

Soham

1 of 1 people found this helpful
• ###### 5. Re: count distinct id percent of total more than 100

unfortunately cant share the workbook. pls let me know if you need any more info.

i am doing a calculated filed countD(Patient iD) for a source lets say A & B.

pulling that calculated filed to label and the add table calculation - across table. calculation type -percent of total.

• ###### 6. Re: count distinct id percent of total more than 100

HI Deepak,

Have you tried the approach that i have suggested ??

• ###### 7. Re: count distinct id percent of total more than 100

yes. didnt work.

• ###### 8. Re: count distinct id percent of total more than 100

Works great here. I'm sure glad I'm not having to do ETL ... Thank you very much Soham Wadekar. Always good to meet awesome LOD calculators.

playing with something like...

SUM({Fixed [dim]:COUNTD([Id])}) /

TOTAL(SUM({Fixed [dim]:COUNTD([Id])}))

Big help for me, cheers!

Tyler

• ###### 9. Re: count distinct id percent of total more than 100

This approach works after utilizing the fixed [dim] approach explained above. Thanks for your visual help with screenshots, I ended up doing exactly what you did too, that's a great recommendation if you're not looking to type out the percent of total calculation. Some users may not be materializing it into a standalone calculation. Which stands to say, your solution is accurate too. I believe your count(d) is at a unique granularity, which then makes it appear to work for your data source. And that's because you're looking at order data from an order table. If it were a different granularity, higher than the most distinct value in the data source, then it would generate the oddity we are seeing utilizing count(d) in 'particular' data sources...

• ###### 10. Re: count distinct id percent of total more than 100

True VS False, has a % of 100, if you're looking at a pie chart it can push you this particular direction.

When your equation calls for a countd() of a dimensions that has a date, and happens more than once, if there's a fluctuation, understanding what the most relevant value may offer a more concurrent view of the data, will help these values equalize.

Example;

An ID flag changes between true and false each month.

And you may want to add your true false to the viz. And do a percentage of total.

That's two count distincts of two slices of the same dimensions. Counting the id can lead to these irregular percentages.

It helped to understand my particular ID was able to swap between true and false, in a date range, and tableau is computing the correct percentages... but laying on a pie chart, these percentages seem misleading. Pie is 100%...

Total() vs Window_Sum()

This link above helped me a bit during the reading.

Cheers.