Calculating percentage corresponding to blank cells
Hi all,
I want to calculate percentage of the events count in my dataset like below
Here, we can say for the month of August, there are 67 events with flag=true. Hence, we can see 100% displayed next to it.
Similarly, for the month of September, there are 3 Outcome2 events with flag=true. And the percentage comes out to be (3/131(since 131 is the total for September month) )*100 i.e. 2.29%.
Till here, month wise calculations look good.
Problem comes when corresponding percentage is calculated in subtotals in columns.
For calculation of percentage, I'm using below formula:
(sum(sum(`count`) FIXED (BY `name`, `outcome`, `flag`, `year`, `month`))/ sum(sum(`count`) FIXED (BY `name`, `year`, `month`)) ) *100
I've also added a denom column separately to observe the value of percentage.
Formula used for denom column is the section after "/" in above formula:
sum(sum(`count`) FIXED (BY `name`, `year`, `month`))
I can see, that denom value changes to 131 starting from Outcome3, which affects the percentage calculation for subtotal column.
My expectation is, if I collapse everything in my pivot table like this:
The sum of percentages with values True and False should be 100.
But, due to blank cells, denominator values gets messed up, which results in incorrect percentage calculation and hence, I see overall sum greater than 100.
Initially, I thought if I could place zeros in blank cells, maybe that will help. But looks like that's not possible if those rows do not exist in my dataset https://dojo.domo.com/main/discussion/55936/fillingblankcellswith0/p1?new=1
I tried to think of some alternate way to represent the data, but haven't come across so far.
Reason, why I need subtotal column: In above snip, we could see, events count information month wise. However, there can be situations, where, I need hour wise information. In that case, I need to provide subtotal column, so that user doesn't have to add everything up, if he's just interested in total events count in given time frame.
Let me know, if any other information is needed from my end.
Answers

If anyone wants to look into it, he can use this sample data
Below is the screenshot of above sample data
Highlighted percentages for respective month of August and September is correct.
Problem comes only for subtotal calculation corresponding to empty fields.
Red Underlined percentage in subtotal column is incorrect. It should have displayed the value 2/198 which is 1.01
198 because for name="Test name" we have total 198 events.
Logic used for %_calc column:
(sum(sum(`count`) FIXED (BY `name`, `flag`, `outcome`, `year`, `month`))/ sum(sum(`count`) FIXED (BY `name`, `year`, `month` )) ) *100
0
Categories
 All Categories
 1.5K Product Ideas
 1.5K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 285 Workbench
 4 Cloud Amplifier
 5 Federated
 2.7K Transform
 91 SQL DataFlows
 575 Datasets
 2.1K Magic ETL
 3.5K Visualize
 2.4K Charting
 619 Beast Mode
 25 App Studio
 33 Variables
 609 Automate
 155 Apps
 421 APIs & Domo Developer
 31 Workflows
 2 DomoAI
 29 Predict
 12 Jupyter Workspaces
 17 R & Python Tiles
 372 Distribute
 103 Domo Everywhere
 266 Scheduled Reports
 3 Software Integrations
 100 Manage
 97 Governance & Security
 4 Domo University
 15 Product Releases
 Community Forums
 37 Getting Started
 28 Community Member Introductions
 91 Community Announcements
 4.8K Archive