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.3K Product Ideas
 1.3K Ideas Exchange
 1.4K Connect
 1.1K Connectors
 281 Workbench
 2 Cloud Amplifier
 4 Federated
 2.8K Transform
 81 SQL DataFlows
 541 Datasets
 2.2K Magic ETL
 3.1K Visualize
 2.3K Charting
 514 Beast Mode
 25 Variables
 555 Automate
 129 Apps
 408 APIs & Domo Developer
 18 Workflows
 27 Predict
 11 Jupyter Workspaces
 16 R & Python Tiles
 342 Distribute
 84 Domo Everywhere
 257 Scheduled Reports
 1 Software Integrations
 81 Manage
 80 Governance & Security
 2 Product Release Questions
 Community Forums
 41 Getting Started
 27 Community Member Introductions
 75 Community Announcements
 4.8K Archive