Issues getting a true distinct count of the total dataset

#1

I have a datacube where each Associate has 5 strengths, so their name will be repeated 5 times. I am building a chart with the percentage of Associates who have each strength, so I need the percentage over the total number of Associates, not Associate strengths (which would be 5 times the total number of Associates.

It would replicate this, and the calculation is just a distinct count of Associates using a table calculation set to Percent of Total computed using Table (down) in Tableau:

image
image.png1168×665 39.3 KB

image

I am unable to replicate this in Dundas. PERCENTTOTAL and PERCENTTOTALGROUP both seem to use the total number of rows, not Associates. I thought perhaps I could trick it into getting the total for the whole dataset by creating a field that is the same for everyone and using SUM() to group the distinct Associate count by that, but no luck. I am able to get close by manually dividing the number by 5, but as some people mis-entered their strengths, it is slightly off, and I’d prefer if I could get the true number of Associates rather than a number calculated based on how many strengths they entered.

How can I do this?

#2

Hi,

Yes, in metric set level, a DCT of a field will give distinct count/count of values in that field based on the hierarchy the metric set has under ROWS, because the aggregator for a measure always takes the hierarchies into account. The grand total, however, will show distinct count/count of specific values disregarding the hierarchy.

In your case, you can calculate just associate_count (Drag Associates under Measures with DCT in a metric set) and then associate_per_strength in another metric set. Next, use a formula visualization to calculate associate_per_strength /associate_count and visualize that as bar chart - something like below:
image

1 Like
#3

Thanks, I’ll give it a try!

#4

I was able to get the bar chart but unable to assign colors using domains the way I had on the original chart. I tried using states instead but no luck. Putting domains on the rows at all seems to lump everything into one bar. Not sure what I’m missing.

#5

What does the metric set look like? And what are you trying to do do?

#7

I think I figured it out! I need to make sure the tables for this are set up the way the final needs to be (have domains on rows, etc.) and not try to edit the formula viz (putting domains on rows there makes it show only one bar), I think.

#8

Spoke too soon, now I’m having trouble getting it to filter with the other charts on the dashboards. I have all the parameters hooked up to the correct hierarchies, but the values in the viz don’t change.

#9

Are those viewParameters connected to the Formula Visualization as well? They need to be.

#10

Yes, they are. Connecting the metric sets the formula viz was based on also didn’t help.

#11

Solved in the meeting.

1 Like
#12

Thank you @upasana!! You helped me out a ton today.

(I needed to make sure slicers are added to original metric sets and NOT added to the formula viz, then I could select those in the parameters rather than the same hierarchies that were under slicers on the formula viz)

1 Like