Trouble using pivot tables to calculate field and subfield values.
Our take
I am having trouble getting a clean summary of our monthly stats for a surgical practice I work for. I am using a pivot table connected to the surgical case spreadsheet. In the spreadsheet, each row represents an individual case.
I am struggling to get these values in the same field:
1) Monthly total of days the practice performed surgery
2) Subfields showing Monthly total of days each doctor performed surgery
The issue is that I cannot figure out how to calculate both of these in the same field. "Sum of Days Worked" returns the correct monthly total, but the wrong total of days for that individual doctor. This is because a "1" gets placed in this column next to the first surgery of the day. So, that value only gets assigned to the doctor who performed the first surgery of the day. "Sum of Dr Day" returns a "1" on the first surgery each doctor performed that day. This calculates the correct total of days each doctor performed surgery, but the monthly total is off, because if there are more than one scheduled per day, it counts the day twice.
I understand I can achieve the desired totals with regular formulas, but we are constantly having new doctors join and leave our practice, so I prefer pivot tables because I don't have to continuously adjust formulas.
Google tells me "Add this Data to the Data Model" is helpful for this problem, but we only use Macbooks at work. Any help is appreciated- this is driving me crazy.
[link] [comments]
Read on the original site
Open the publisher's page for the full experience