3 Replies Latest reply on Nov 10, 2015 6:41 AM by jasheldo

    How do I calculate a percentage in a summary report on two fields in the same line?


      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.


      Screen Shot 2015-11-09 at 8.08.05 PM.png

        • 1. Re: How do I calculate a percentage in a summary report on two fields in the same line?

          Are the figures 17,909,386 and 34,752,939 themselves summary data (in other words, is each line of your report a sub-summary)? If so, you will need to use the GetSummary () function in order to perform calculations with it.

          On the other hand, if each line is a single record it is a simple matter of calculating the appropriate percentage from the data fields. You might even be able to do this anyway, and then average the individual percentages as your summary field, but I'd need to test that to see if it produces the right result.

          • 2. Re: How do I calculate a percentage in a summary report on two fields in the same line?

            Good morning!  Yes, each quantity you see in this report is a summary.  Would you mind explaining more about how I can use the GetSummary() function to make this work?  Do I create a field in the table?  I've tried your suggestion a few ways and while I feel it puts me on the right track I'm still floundering on using it properly.


            Thank you!

            • 3. Re: How do I calculate a percentage in a summary report on two fields in the same line?

              Thank you again for the assist.  Unless there's a more simple way, here's what I did based on your comments.  I created a field that is the following calcuation:


              GetSummary( Summary of Payment ; Service Category ) / GetSummary( Summary of Charges ; Service Category ).


              Adding that field to the individual categories gave me the correct percentages.  Additionally I had to create fields that captured the summary based on the other summaries I have in the report.  So I have these additional field I added:


              GetSummary( Summary of Payment ; Product ) / GetSummary( Summary of Charges ; Product )

              GetSummary( Summary of Payment ; Patient Type ) / GetSummary( Summary of Charges ; Patient Type )

              GetSummary( Summary of Payment ; Hospital ) / GetSummary( Summary of Charges ; Hospital )

              Adding these new fields to the appropriate section of the report gave me the correct calculations.  If there's a way to only have one field to do all the heavy lifting instead of four I'd be all ears.


              Thank you keywords for putting me on the right track.

              1 of 1 people found this helpful