you need to aggregate the fixed values in the % calculation
the combined then becomes
and it returns this
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
Thanks so much Jim,
I am not sure why I was having such trouble finding that one. But yes it works like a charm and will make my life a lot easier going forward.
Not to reopen this discussion but something strange is happening now. It works great on the small scale like I submitted. However ran across this issue:
When I expand it and a location has Referrals for June but no Enrollments- they are not being added in to the total calculation for the %. It is not what I would expect.
Location C- for June has 5 Referrals but no Enrollments. Those 5 Referrals are not being added to the Denominator for total %.
Any suggestions for this?
I am working on a solution - it has to do with totals and the LOD's but I ran into a road block
see below - the formula is going to calculate totals and place them in the total column - but I get the wrong answer (.72) for the ratio
I broke out the numerator and it total 18 (see red circle) - I tried a number of different approaches (LOD's, widow sum) to get to 15 but can't find where it is going wrong
is there anything in the data that would account for the difference
So what I found in the data is that It seems to miss the locations that don't have any information in the Enrolled Date Column- meaning they are blank.
Orange County Med and San Bernardino Med you can see have Internal Referrals section but no Enrolled % section. This can only be accounted by the fact that they each have blanks in the data date for the Enrolled section.
I'm thinking at this point I may talk to them and say they are too new to report but I don't particularly like that answer either.