Forum Discussion
Help with formula to maintain months when calculating average
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:
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])
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])))
5 Replies
- littlemojopuppy
Community Champion
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...
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
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])
- amitchandak
Super User
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]))) - v-robertq-msft
Community Support
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.
