Measure for powerpivot to create average of utilization of total population of different field

[SamT87]

Hi All - 

I am hoping someone can help me with a question rather quickly.  

I'm trying to calculate the average of the Sum of "Total Units" in an entire population.  My current measure formula is only displaying the average of total units of the total population at the CPT Code level opposed to the average units of the CPT Code at the entire population level (field "Claim Number"). 

The challenge that exists is there could be "Claim Numbers" that do not contain specific CPTs.  However, I want the calculation of the "average units" of the CPT utilization to take into account the ENTIRE population so the average is representative of the entire population.  I'm not well versed at editing tables outside of excel, so my preference is to use a measure formula.  However, if this cannot be achieved be creating a powerpivot measure I am open to trying to trying something through the "manage" Data Model.  The other option is to paste all CPTs for all claim numbers with a unit frequency of zero, but that would not be efficient for future updates and also likely get into the millions of rows scenarios.

Current Measure Formula

'=DIVIDE(Sum('Range 1'[Units Billed]),DISTINCTCOUNT('Range 1'[Claim Number]))

Example current output (incorrect as it is only calculating the average utilization of the total of CPT units (97530) 

Example Pivot Structure

Appreciate the help and consideration!

Thank you,

Sam

[SamT87 · Accepted solution]

Switched to Power BI and learned how to do a calculate function. Problem solved, boom!

Avg Units = divide(sum('Detailed Data CPT AND CHARGE'[Units Billed]),CALCULATE(DISTINCTCOUNT('Detailed Data CPT AND CHARGE'[Claim Number]),ALL('Detailed Data CPT AND CHARGE'[CPT Code])))

[SamT87]

😞