Average grouped by filters as a column in a table

[Anonymous]

I have a table in summary page which has average per location by months (Sum of new patients divided by count of locations), I want to have a two columns average new patients by location in state and bucket level, which are my two filters. I have attached my pbix file here. SALESCopy.pbix

[Anonymous]

Hi @amitchandak ,

I am trying to compare average of new patients per loc by average of new patients per loc by state, so one account would have average of new patients per loc, and average in state would be the sum of new patients in that state (multiple accounts) / no of locations in that state.

The two tables below were for reference, so I need a state-wise average as well, and the numbers are correct in that table .

[amitchandak]

@Anonymous , Your measure needs to be like

Average NewPatient per location =
divide(sum(NewPatient[NewPatientsCount]), count(Location[c_LocationKey]))

Also, not sure why there different table for state, bucket , Using above measure and set of dimesion it should come from one table

example

Average New Patients by State =
CALCULATE (
SUM ( [Average NewPatient per location],
ALLEXCEPT (Location,  Location[State] )
)

In case they are coming from different tables from source, then create common dimensions and have mesures like the first one

Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bda

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.