% of Total Using Distinct Count

user12758

I need to show a % Total of Accounts in the below card. I used the following to get the below % of Total accounts. However, there are multiple rows per account name. The # of Accounts column is calculating a Distinct Count, but the % of Total appears to be calculating of the # of rows, not a distinct count of the Account Name.

SUM(SUM(1)) OVER (PARTITION BY `Status`) / SUM(SUM(1)) OVER ()

Is there a way to show # of distinct accounts per status / total # of unique accounts?

GrantSmith

Try SUM(COUNT(DISTINCT `Account Name`))

jaeW_at_Onyx

you can't do a count(distinct) across rows in a table card. if you take the sum of count(distinct) it assumes that an account does not exist across multiple statuses.

unfortunately, you cannot count(count(distinct)) b/c the number of accounts would have already been aggregated.

instead you could UNION a set of rows containing STATUS = 'Unique Accounts' with one row per account to the dataset.

tlammie1806

@user12758

I ran into a similar problem where...

I was also trying to calculate % of Total Unique Records but kept getting % of Total Records when I set up the calculation as COUNT(DISTINCT 'id field') / COUNT(DISTINCT 'id field') FIXED()

I did what @jaeW_at_Onyx suggested (or at least a version of it), where I identified the first instance of all "duplicates" in the dataset and tag it as "unique" in an identifier column so that I can set the denominator to count values in this unique identifier column instead.

To set up this unique identifier column I used Rank & Window to get the Row Number partitioned on the field where the unique ID is supposed to be (in my case Tracking Number).

Once I have the column that identifies just the first instance of all duplicate rows, I set the calculation to be COUNT(DISTINCT 'id field') / COUNT('unique record') FIXED (), and it worked!

DavidChurchman

Here's an example Magic ETL that does this:

This creates a column that has the Total # Accounts on every row

You can then use the average of that as your denominator in the BeastMode:

COUNT(DISTINCT `Account Number`)/avg(`Total # Accounts`)

RFR

Hi there,
I had an issue with this as well, and as I also needed to use filters for my dashboard, these useful tips above were irrelevant. After watching this excellent video by jaeW_at_Onyx : https://www.youtube.com/watch?v=Xb4QgKYgaqg I realized I could add another column, and not use fixed feature at all.

If you need to use a fixed count distinct because you need to use the filter deny feature, then instead you can create another ID column that is only populated when your the row meets your criteria, and then you simply do : count( distinct subset_ID ) / count( distinct ID )

This is how you get percent out of total that can also work with filters.