% 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.

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?


Answers

  • GrantSmith
    GrantSmith Indiana 🥷

    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!**
  • jaeW_at_Onyx
    jaeW_at_Onyx Budapest / Portland, OR 🔴

    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 ⚪️
    edited November 15

    @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!


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