Having trouble with total averages

[cathoms]

I am trying to build a dataset/model to analyze the utilization rates of codes for office visits. Established office visits can be coded 99212, 99213, 99214, or 99215. Right now my data model is really just a flat table with the following fields.

Period

NPI

Last Name

First Name

Full Name

CMS Speciality

Primary Taxonomy

Group

Total Est Visits

99212 Freq

99213 Freq

99214 Freq

99215 Freq

I'm playing around with this in Excel Power Pivot before I work in Power BI. I've created a couple of measures to calculate the utilization rate for each code (i.e. code Freq / Total visits). I want to compare individual provider utilization rates with the average utilization rate for each code. My measures keep giving me row by row rates, not a total rate:

I want a way to calculate the same values in the Grand Total row so that I can make comparisons. Here's what I have tried.

Avg 99212 Util:

= AVERAGE(All_Visits[99212 Rate])

Avg 99212 Rate:

= [Total 99212 Visits] / [Established Visit Total]

Ultimately the goal is to flag providers based on how their utilization rates compare to the average within their primary taxonomy. So, for example, I want to flag provider's whose utilization rate for 99212 is more than 10 percentage points different than the average of provider's in their same grouping, e.g. Adult Health.

[v-yangliu-msft]

Hi @cathoms ,

If you want new ideas, you can continue to ask questions.

If you want to close the thread, you can select the above ellipsis and select Delete to delete the post.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yangliu-msft]

Hi  @cathoms  ,

That's great, you can share sample data and sample output in tabular form, using tabular data or pictures to describe the results you need and detailed intermediate process logic, we can better help you.

Best Regards,

Liu Yang

[cathoms]

This is no longer relevant as the source data file is now different. Not sure how to "close" this thread, however, as there is no solution, per se...

[v-yangliu-msft · Accepted solution]

Hi  @cathoms  ,

This is the data I created:

Here are the steps you can follow：

1. Create measure.

Measure =
DIVIDE(MAX('All_Visits'[99212 Visits]),MAX('All_Visits'[Established Visit Total]))

Measure 2 =
VAR _1=
SUMX(FILTER(ALL('All_Visits'),'All_Visits'[Row labels]=MAX('All_Visits'[Row labels])),[Established Visit Total])
VAR _2=
SUMX(FILTER(ALL('All_Visits'),'All_Visits'[Row labels]=MAX('All_Visits'[Row labels])),[99212 Visits])
RETURN
DIVIDE(_2,_1)

Measure 3 = IF([Measure]<[Measure 2],1,0)

2. Result:

If my answer does not meet your expectations, can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[cathoms]

Thank you. Unfortunately, that did not work. I will try to find time to create and share some sample data and additional details.

[cathoms]

One other more basic question is how I should best set up the data model for this work.