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.7K Product Ideas
 1.7K Ideas Exchange
 1.5K Connect
 1.2K Connectors
 292 Workbench
 4 Cloud Amplifier
 8 Federated
 2.8K Transform
 95 SQL DataFlows
 600 Datasets
 2.1K Magic ETL
 3.7K Visualize
 2.4K Charting
 685 Beast Mode
 43 App Studio
 38 Variables
 655 Automate
 170 Apps
 438 APIs & Domo Developer
 42 Workflows
 5 DomoAI
 32 Predict
 12 Jupyter Workspaces
 20 R & Python Tiles
 383 Distribute
 110 Domo Everywhere
 267 Scheduled Reports
 6 Software Integrations
 111 Manage
 108 Governance & Security
 8 Domo University
 25 Product Releases
 Community Forums
 39 Getting Started
 29 Community Member Introductions
 98 Community Announcements
 Domo Community Gallery
 4.8K Archive