# % of Total Using Distinct Count

Options
Member

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?

• Coach
Options

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

**Did this solve your problem? Accept it as a solution!**
• Coach
Options

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.

Jae Wilson
Check out my 🎥 Domo Training YouTube Channel 👨‍💻

**Say "Thanks" by clicking the ❤️ in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"
• Member
edited November 2022
Options

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!

• Coach
Options

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