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 the heart icon**
**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 the heart icon**
**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
- APAC User Group
- 12 Welcome
- 36 Domo News
- 9.6K Using Domo
- 1.9K Dataflows
- 2.4K Card Building
- 2.2K Ideas Exchange
- 1.2K Connectors
- 338 Workbench
- 250 Domo Best Practices
- 11 Domo Certification
- 460 Domo Developer
- 47 Domo Everywhere
- 100 Apps
- 703 New to Domo
- 84 Dojo
- Domopalooza
- 1.1K 日本支部
- 4 道場-日本支部へようこそ
- 22 お知らせ
- 62 Kowaza
- 295 仲間に相談
- 649 ひらめき共有