% of Total Using Distinct Count

user12758
user12758 Member
in Charting

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?

image.png


Answers

  • GrantSmith
    GrantSmith Coach

    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 Coach

    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

    @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()

    image.png

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

    image.png

    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!

    image.png


  • DavidChurchman
    DavidChurchman Coach

    Here's an example Magic ETL that does this:

    image.png

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

    image.png

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

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

    image.png


    Please 💡/💖/👍/😊 this post if you read it and found it helpful.

    Please accept the answer if it solved your problem.

  • RFR
    RFR Member

    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.

Categories