Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Anonymous
Not applicable

Average per institution per month

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:

 

InstitutionTotalChecksDateExportMonthYear

A

5025/11/2020112020
A5220/11/2020112020
A5014/06/2020062020
A4912/06/2020062020
B1025/11/2020112020
B1020/11/2020112020
B1214/06/2020062020
B1212/06/2020062020
C7525/11/2020112020
C7520/11/2020112020 
C7314/06/2020062020
C7212/06/2020062020

A

5020/01/2021012021
A5010/02/2021022021
A481/03/2021032021
A471/06/2021062021
B1520/01/2021012021
B1510/02/2021022021
B151/03/2021032021
B151/06/2021062021
C7020/01/2021012021
C7310/02/2021022021
C681/03/2021032021
C601/06/2021062021

 

I'm expecting it to look something like this:

 

InstitutionAverageChecksMonthYear
A51112020
A50.5062020
A50012021
A50022021
A48032021
A47062021
B10112020
B12062020
B15012021
B15022021
B15032021
B15062021
C75112020
C73.5062020
C70012021
C73022021
C68032021
C60062021

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!

1 ACCEPTED SOLUTION

@Anonymous , 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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@Anonymous , Try a measure like

 

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube
Anonymous
Not applicable

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])

@Anonymous , 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

 

 

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!