COUNT(DISTINCT *) desconsidering only one filter
Hi, People! I'm here again! The DOMO noob! π
I'm stuck with the beastmode problem:
Imagine that i have 4 cities e each one of it has a N number of reseller points.
In my table, i need to show that N number of reseller points considering all the filters of the page (globals and selections in cards) into one column and in another column, i need to show the [Absolute Number Of Reseller Points] that considers all the filters, except one.
That's what i have till now:
[N number of reseller points considering all the filters] = "Posit. At"
[considers all the filters, except one] = "Posit. Abs"
In the image above, i didn't use the filter, so the both column has the same value, OK.
Now, the problem:
In the image above, i used the filter, so the [Posit. At] has the correct value and the [Posit. Abs] should have 7.130.
That's the script of [Posit. Abs]:
"AVG(COUNT(DISTINCT `RETAILER_CODE`)) OVER (PARTITION BY `CITY_NAME`)"
Any ideas to help this noob? π
Best Answers

Filters will apply to all rows in your dataset so what youβre wanting to do isnβt possible. You might be able to get around it using a segment and tell it to not apply those filters you wish to ignore.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
you cannot do a count distinct across all your cities even with a window function.
the window function would calculate count distinct for EACH city, and then take the average, min or max as you've tried already.
unfortunately (as i'm sure you've figured out) if i have the same retailer code in multiple cities, it would double count.
what you would have to do is restructure your data. easiest thing to do would be to UNION the entire dataset onto itself such that the second grouping contains 'All Cities' in the City column.
then you can construct your metric using beast modes
count (distinct case when city <> 'all cities' then `retailer code`) / max(count (distinct case when city = 'all cities' then `retailer code`)) over ()
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"1
Answers

Ah... One little problem!
Using ETL is not possible because the base is only a Snowflake view.
The data is not in DOMO DW.
π₯
0 
Filters will apply to all rows in your dataset so what youβre wanting to do isnβt possible. You might be able to get around it using a segment and tell it to not apply those filters you wish to ignore.
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**1 
you cannot do a count distinct across all your cities even with a window function.
the window function would calculate count distinct for EACH city, and then take the average, min or max as you've tried already.
unfortunately (as i'm sure you've figured out) if i have the same retailer code in multiple cities, it would double count.
what you would have to do is restructure your data. easiest thing to do would be to UNION the entire dataset onto itself such that the second grouping contains 'All Cities' in the City column.
then you can construct your metric using beast modes
count (distinct case when city <> 'all cities' then `retailer code`) / max(count (distinct case when city = 'all cities' then `retailer code`)) over ()
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"1 
Tanks, @GrantSmith and @jaeW_at_Onyx !
Our team is stuck in some fronts just because we cannot use MagicETL and burocracy to change the way we receive the data (other enterprise).
I tried some ways, but without part of DOMO features, (ETL, Explorer, etc) some visualizations are simply not possible.
Tks again for the quick response!
0
Categories
 All Categories
 1.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 602 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 695 Beast Mode
 43 App Studio
 39 Variables
 658 Automate
 170 Apps
 441 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 386 Distribute
 111 Domo Everywhere
 269 Scheduled Reports
 6 Software Integrations
 113 Manage
 110 Governance & Security
 8 Domo University
 30 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive