Creating Percentages for table groupings
I am trying to create a percentage for my dataset that will give me the percent of observations in multiple levels of groupings. So if my data has 2 levels of grouping, then the table would look like this:
group1 group2 count percent_Grouping1(A or percent_Grouping2(AA,AB,BA,BB)
a a 10 33.33% 45.45%
a a 4 13.33% 18.18%
a a 8 26.66% 36.36%
a b 2 6.66% 25%
a b 6 20% 75%
a b 0 0% 0%
b a 9 18% 27.27%
b a 13 26% 39.39%
b a 11 22% 33.33%
b b 7 14% 41.18%
b b 6 12% 35.29%
b b 4 8% 23.53%
I am looking for a way to get the percent of records in that level of grouping. In my actual data I have 5 levels of grouping that I need to consider, but I think this examples get my need across. I have been trying to write a beast mode caluculation to run this, but I haven't found a working solution. Any help as either a beastmode calculation or something else would be greatly appreciated.
Comments
-
So you'll need to actually get the counts for the possible groups created via an ETL/transform and added in as constants to your dataset. That way you can do something like :
`count`
/
CASE WHEN `group1` = 'a' AND `group2` = 'a' THEN MAX(`aaTotal`)
WHEN `group1` = 'a' AND `group2` = 'b' THEN MAX(`abTotal`)
WHEN `group1` = 'b' AND `group2` = 'a' THEN MAX(`baTotal`)
WHEN `group1` = 'b' AND `group2` = 'b' THEN MAX(`bbTotal`)
ENDLet me know if you have questions on setting this up.
Sincerely,Valiant
**Please mark "Accept as Solution" if this post solves your problem
**Say "Thanks" by clicking the "heart" in the post that helped you.0
Categories
- All Categories
- 1.9K Product Ideas
- 1.9K Ideas Exchange
- 1.6K Connect
- 1.3K Connectors
- 302 Workbench
- 6 Cloud Amplifier
- 9 Federated
- 2.9K Transform
- 104 SQL DataFlows
- 637 Datasets
- 2.2K Magic ETL
- 3.9K Visualize
- 2.5K Charting
- 762 Beast Mode
- 65 App Studio
- 42 Variables
- 704 Automate
- 182 Apps
- 458 APIs & Domo Developer
- 53 Workflows
- 11 DomoAI
- 39 Predict
- 16 Jupyter Workspaces
- 23 R & Python Tiles
- 401 Distribute
- 116 Domo Everywhere
- 277 Scheduled Reports
- 8 Software Integrations
- 133 Manage
- 130 Governance & Security
- 8 Domo Community Gallery
- 38 Product Releases
- 12 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 111 Community Announcements
- 4.8K Archive