Calculating total against subtotal
Hi,
I have a data set as follows:
ID | Name | Condition1 | Condition2 | Condition3 | Condition4 |
123 | ABC | 1 | 1 | 0 | 1 |
245 | XYZ | 1 | 0 | 1 | 1 |
789 | PQR | 0 | 0 | 1 | 1 |
Which I need to represent as :
Count | % | |
Con1 | 2 | 70 |
Con2 | 1 | 34 |
Con3 | 2 | 70 |
Con4 | 3 | 100 |
I am using mysql and have converted the data as:
ID | Name | Condition1 | Condition2 | Condition3 | Condition4 | Condition |
123 | ABC | 1 | 0 | 0 | 0 | Con1 |
123 | ABC | 0 | 1 | 0 | 0 | Con2 |
124 | ABC | 0 | 0 | 0 | 1 | Con4 |
245 | XYZ | 1 | 0 | 0 | 0 | Con1 |
245 | XYZ | 0 | 0 | 1 | 0 | Con3 |
245 | XYZ | 0 | 0 | 0 | 1 | Con4 |
789 | PQR | 0 | 0 | 1 | 0 | Con3 |
789 | PQR | 0 | 0 | 0 | 1 | Con4 |
But for the % calculation, I need the total number of records in beast mode. My beast mode:
Count(Condition)/ sum(count(distinct ID) over ( )
e.g. for Con1 % calculation should be 2/3 whereas when I use window function in beast mode it gives 2/8. Is there nay way to handle it in beast mode or mysql?
Comments
-
Hi @user016969
You're close but instead of using a window across the entire dataset you're wanting it across each ID / Name.
Just a minor change to your beast mode should get you what you want.
Count(Condition)/ sum(count(distinct ID) over (PARTITION BY `ID`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
That is giving me 100% for all rows. I added a column just for the beast mode sum(count(distinct ID) over (partition by ID) and it gives same # as condition count.
0 -
Instead of using COUNT try using SUM:
SUM(Condition1)/ sum(count(distinct ID)) over (PARTITION BY `ID`) SUM(Condition2)/ sum(count(distinct ID)) over (PARTITION BY `ID`) SUM(Condition3)/ sum(count(distinct ID)) over (PARTITION BY `ID`) SUM(Condition4)/ sum(count(distinct ID)) over (PARTITION BY `ID`)
**Was this post helpful? Click Agree or Like below**
**Did this solve your problem? Accept it as a solution!**0 -
That's giving 0%
0 -
In the absence of other solution, through mysql I added a static column in table to store the total number of unique records and using that value in beast mode.
0
Categories
- 10.6K All Categories
- 8 Connect
- 918 Connectors
- 250 Workbench
- 477 Transform
- 1.8K Magic ETL
- 69 SQL DataFlows
- 478 Datasets
- 218 Visualize
- 260 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
- 191 Product Ideas
- 1.2K Ideas Exchange
- 11 Community Forums
- 27 Getting Started
- 14 Community Member Introductions
- 55 Community News
- 4.5K Archive