% 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
-
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!**0 -
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"0 -
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!0 -
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`)
0
Categories
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 474 Transform
- 1.7K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 214 Visualize
- 259 Beast Mode
- 2.1K Charting
- 12 Variables
- 19 Automate
- 356 APIs & Domo Developer
- 89 Apps
- 3 Workflows
- 20 Predict
- 5 Jupyter Workspaces
- 15 R & Python Tiles
- 249 Distribute
- 65 Domo Everywhere
- 243 Scheduled Reports
- 21 Manage
- 42 Governance & Security
- 190 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive