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
 10.5K All Categories
 5 Connect
 915 Connectors
 250 Workbench
 459 Transform
 1.7K Magic ETL
 69 SQL DataFlows
 476 Datasets
 185 Visualize
 250 Beast Mode
 2.1K Charting
 11 Variables
 16 Automate
 354 APIs & Domo Developer
 88 Apps
 3 Workflows
 20 Predict
 5 Jupyter Workspaces
 15 R & Python Tiles
 245 Distribute
 62 Domo Everywhere
 242 Scheduled Reports
 20 Manage
 41 Governance & Security
 170 Product Ideas
 1.2K Ideas Exchange
 10 Community Forums
 27 Getting Started
 14 Community Member Introductions
 55 Community News
 4.5K Archive