% 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`)