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.4K Product Ideas
- 1.4K Ideas Exchange
- 1.4K Connect
- 1.2K Connectors
- 284 Workbench
- 4 Cloud Amplifier
- 4 Federated
- 2.9K Transform
- 88 SQL DataFlows
- 554 Datasets
- 2.2K Magic ETL
- 3.3K Visualize
- 2.3K Charting
- 561 Beast Mode
- 8 App Studio
- 27 Variables
- 577 Automate
- 140 Apps
- 414 APIs & Domo Developer
- 22 Workflows
- 1 DomoAI
- 28 Predict
- 12 Jupyter Workspaces
- 16 R & Python Tiles
- 350 Distribute
- 90 Domo Everywhere
- 258 Scheduled Reports
- 2 Software Integrations
- 91 Manage
- 88 Governance & Security
- 9 Product Release Questions
- Community Forums
- 41 Getting Started
- 27 Community Member Introductions
- 85 Community Announcements
- 4.8K Archive