POWERBI/EXCEL: Help to find the monthly average of a customer's transaction

**PrimeVandal** · 08-16-2024, 02:55 AM

sample_sheet.xlsx

Hello, i have a sample sheet here

I dont expect anyone to do the work for me but i wish to be pointed in the right direction.
Based off this excel data sheet - i wish to know how to find out the monthly average transaction amount that customers put in, in a power bi visual.

i initially used a power bi card visual where i used the measure = AVERAGE('Table'[Transaction])
However, i dont think it's right as the overall transaction average and not the monthly average customers put in.
Would appreciate any next steps. Thank you

**AliGW** · 08-16-2024, 03:14 AM

Administrative Note:

Welcome to the forum.

Is your forum profile showing the oldest Excel PRODUCT that you need this to work for?

Members will tailor the solutions they offer to the Office PRODUCT (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your product is for Mac, please also state this.

The three most recent Excel products are Excel 2019, Excel 2021 and MS365 - if you are using MS365, please give this name along with the version number in your profile (e.g. MS365 Version 2306). This is in the About Excel section further down the Account page.

Thanks.

I've moved this to a different forum section where I think you'll be more likely to be noticed by Power BI users. However, I should warn you that we get very few questions on Power BI and I'm not sure that I've ever seen any of them answered.

**AliGW** · 08-16-2024, 03:37 AM

If you have 365 for Mac (please update your forum profile, as requested above), you could use this formula to get a weighted monthly average (assuming 28 days for February):

=LET(m,SEQUENCE(12),md,DAY(EOMONTH(DATE(2023,m,1),0)),AVERAGE(MAP(m,md,LAMBDA(x,y,SUMPRODUCT((MONTH(A2:A29063)=x)*C2:C29063)/y))))

See if it gives what you are expecting.