Calculating percentage corresponding to blank cells

akki
akki Member
edited March 2023 in Datasets

Hi all,

I want to calculate percentage of the events count in my dataset like below

image.png

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:

image.png

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/filling-blank-cells-with-0/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.

Tagged:

Answers

  • akki
    akki Member

    If anyone wants to look into it, he can use this sample data

    Sample_data.xlsx

    Below is the screenshot of above sample data

    image.png

    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

Categories