% of Total Using Distinct Count

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.


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


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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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"
  • tlammie1806
    tlammie1806 Member
    edited November 2022


    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!

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