Help with formula to maintain months when calculating average

[mordy111]

Hoping someone can help me with this basic formula question. I'm trying to calculate the average monthly visits per physician using this formula. This works fine when the visual is filtered by month, however, when bringing the filter out to include multiple months the average obviously is no longer accurate. I do have a table for month groups and was wondering if there is some way to add that filter into the average calculation so that no matter how large the visual is, it will always show me an average based on monthly buckets. 

Here is my current code:

average visits per RENDERING_PROVIDER =

AVERAGEX(

    KEEPFILTERS(VALUES('Providers'[RENDERING_PROVIDER])),

    CALCULATE(SUM('DBAPPOINTMENT'[COUNTAPPOINTMENT]))

)

[littlemojopuppy · Accepted solution]

Try this...

AVERAGEX(
	GROUPBY(
		DateTable,
		DateTable[Year],
		DateTable[Month],
		"Visits",
		AVERAGEX(
			CURRENTGROUP(),
			[Total Visits]
		)
	),
	[Visits]
)

I'm assuming that you have fields for year and month in your date table.  And that there is a measure called "Total Visits" that calculates that (if not just replace with SUM([field for visits])

[v-robertq-msft]

Hi, @mordy111 

According to your description, you want to show the value of the measure which can be grouped by month, I suggest you to use the Matrix chart in Power BI:

You can create a Matrix chart then place the [Month] field in Row, then no matter what the selection of Month Slicer will be, it will always show the value group by month, like this:

More info about creating Matrix in Power BI

If this result is not what you want, you can post some sample data(without sensitive data) and your expected result.

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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

[amitchandak · Accepted solution]

@mordy111 , I think you will need a month year column. Try a measure like

average visits per RENDERING_PROVIDER =
AVERAGEX(
VALUES('Providers'[Month Year]),
CALCULATE(SUM('DBAPPOINTMENT'[COUNTAPPOINTMENT]))
)

or

average visits per RENDERING_PROVIDER =
AVERAGEX(
Summarize('Providers'[RENDERING_PROVIDER],'Providers'[Month Year], "_1"
CALCULATE(SUM('DBAPPOINTMENT'[COUNTAPPOINTMENT]))
), divide(sum([_1]), count([RENDERING_PROVIDER])))

[mordy111]

Using it as a KPI numerical field. So I guess if I select a few months I'm really looking for the average of the average monthly number for the period selected.

(I do also use in a graph with time on axis and it works fine there)

[littlemojopuppy · Accepted solution]

Try this...

AVERAGEX(
	GROUPBY(
		DateTable,
		DateTable[Year],
		DateTable[Month],
		"Visits",
		AVERAGEX(
			CURRENTGROUP(),
			[Total Visits]
		)
	),
	[Visits]
)

I'm assuming that you have fields for year and month in your date table.  And that there is a measure called "Total Visits" that calculates that (if not just replace with SUM([field for visits])

[littlemojopuppy]

I'm wondering if a simple AVERAGE() might suffice.  Depending on how you're going to use this measure, if it's in a matrix or graph with time on the axis, filter context would limit the average to only include those visits in a month in the average calculation...