I have about 4,000 records I'm summarizing in a report. Each record represents one patient, the type of service they had, how much money in billed charges was allowed, how much the insurance company reimbursed on the claim, and how much the hospital is owed in total (insurance claim + patient liability).
I've created a few sub-summary reports to show, by category, the volume, total charges, total paid, total days, etc.... What's completely baffling me is computing a percentage. In the data, on each individual line, this is no problem at all. In fact I scripted it so in case there are material changes to the data through my analysis I can recalculate it quickly. But how do I calculate these percentages on the report?
Attached is a screenshot of what I'm trying to do. For any given line in this report, I want percent values on the right hand side. What I want is, for now, the ( Current Allowed ) divided by ( Charges ). The percentages you see on the screen are wrong. It's my first attempt at this and, like stacked graphs, am finding this obnoxiously more complicated than I feel it should be.
What I should have on the first line all the way to the right is 51.53% (17,909,386 / 34,752,939 ). The second line should be 70.44% ( 899,893 / 1,277,490 ). And so on.
Am I overthinking this and it really is a trivial matter or do I need to change my thinking to get this done?
Thank you in advance for your help.