Average per institution per month

[CaroleU]

Hello,

I'm just starting to use power BI professionally, and I'm trying to calculate something but can't figure out how.

I need the average number of checks per month's time per institution.

We get exports on specific dates with the amount of checks per institution, so we can have several entries for the same institutions for the same month.

My table 'CheckByDate' looks something like this:

Institution	TotalChecks	DateExport	Month	Year
A	50	25/11/2020	11	2020
A	52	20/11/2020	11	2020
A	50	14/06/2020	06	2020
A	49	12/06/2020	06	2020
B	10	25/11/2020	11	2020
B	10	20/11/2020	11	2020
B	12	14/06/2020	06	2020
B	12	12/06/2020	06	2020
C	75	25/11/2020	11	2020
C	75	20/11/2020	11	2020
C	73	14/06/2020	06	2020
C	72	12/06/2020	06	2020
A	50	20/01/2021	01	2021
A	50	10/02/2021	02	2021
A	48	1/03/2021	03	2021
A	47	1/06/2021	06	2021
B	15	20/01/2021	01	2021
B	15	10/02/2021	02	2021
B	15	1/03/2021	03	2021
B	15	1/06/2021	06	2021
C	70	20/01/2021	01	2021
C	73	10/02/2021	02	2021
C	68	1/03/2021	03	2021
C	60	1/06/2021	06	2021

I'm expecting it to look something like this:

Institution	AverageChecks	Month	Year
A	51	11	2020
A	50.5	06	2020
A	50	01	2021
A	50	02	2021
A	48	03	2021
A	47	06	2021
B	10	11	2020
B	12	06	2020
B	15	01	2021
B	15	02	2021
B	15	03	2021
B	15	06	2021
C	75	11	2020
C	73.5	06	2020
C	70	01	2021
C	73	02	2021
C	68	03	2021
C	60	06	2021

Preferably this could be used in a new table as I need to make more calculations with it, but I have no idea how. Any help?

Thanks!

[amitchandak]

@CaroleU , Try a measure like

averageX(summarize(Table, Table[Institution] , Table[Month], Table[Year] , "_1", sum(Table[TotalChecks])),[_1])

[CaroleU]

Hey,

I'm not sure what went wrong here, but if I use this measure I get values for the average that are higher than the max values per month.

For example, I have an institution with 300 checks each month that averages 900 checks in 04/2020 somehow?

It doesn't seem to be averaging several entries per month 😞 

Edit: it does seem to work when I use averageX(summarize(Table, Table[Institution] , Table[Month], Table[Year] , "_1", average(Table[TotalChecks])),[_1])

[amitchandak · Accepted solution]

@CaroleU , I plotted a simple Avg on the data given and other than 2 exceptions all data same as the output

Please find the file attached