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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Gimmerzz
Regular Visitor

PowerBi Averaging issue

Hi, I have a table with 2 months worth of data. The average of all the data is 85% however this is based on PowerBi calculating all of the data across both months. 

 

Instead I need it to calculate the average between the 2 months.

Example:

January is 85% and February is 87% so the average should be 86%

 

I've tried to figure this out but can't do it. What's the easiest way to arrive at 86%. 

 

I will also need this calculation to work as we progress through the year so that I have an average based on all months by the end of the year.

 

Hope someone can help...p.s I'm not good (at the moment) with coding! hoping for a fairly simple fix if possible! 

 

Thanks 

2 ACCEPTED SOLUTIONS
wini_R
Resolver IV
Resolver IV

Hi @Gimmerzz,

 

Assuming you have the following table named tabC:
Year Month Value
2023 Feb 75
2023 Jan 79
2023 Jan 86
2023 Jan 90
2023 Feb 99

You can try the fllowing measure to caclulate the average based on your requirements:

averageByMonth =
VAR _tab =
ADDCOLUMNS(
	SUMMARIZE(
		tabC, tabC[Year], tabC[Month])
	, "avg", CALCULATE(AVERAGE(tabC[Value]))
)
VAR _result = AVERAGEX(_tab, [avg])
RETURN _result

 

wini_R_0-1710003230479.png

 

View solution in original post

Sahir_Maharaj
Super User
Super User

Hello @Gimmerzz,

 

Can you please try the following:

 

1. Calculate Monthly Average

Monthly Average = AVERAGE(YourTable[YourPercentageColumn])

2. Calculate Average of Monthly Averages

Average of Monthly Averages = AVERAGEX(VALUES(YourTable[MonthColumn]), [Monthly Average])

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

View solution in original post

4 REPLIES 4
Sahir_Maharaj
Super User
Super User

Hello @Gimmerzz,

 

Can you please try the following:

 

1. Calculate Monthly Average

Monthly Average = AVERAGE(YourTable[YourPercentageColumn])

2. Calculate Average of Monthly Averages

Average of Monthly Averages = AVERAGEX(VALUES(YourTable[MonthColumn]), [Monthly Average])

Did I answer your question? Mark my post as a solution, this will help others!

If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Sahir Maharaj
Data Scientist | Data Engineer | Data Analyst | AI Engineer
P.S. Want me to build your Power BI solution? (Yes, its FREE!)
➤ Lets connect on LinkedIn: Join my network of 15K+ professionals
➤ Join my free newsletter: Data Driven: From 0 to 100
➤ Website: https://sahirmaharaj.com
➤ Email: sahir@sahirmaharaj.com
➤ Want me to build your Power BI solution? Lets chat about how I can assist!
➤ Join my Medium community of 30K readers! Sharing my knowledge about data science and artificial intelligence
➤ Explore my latest project (350K+ views): Wordlit.net
➤ 100+ FREE Power BI Themes: Download Now
LinkedIn Top Voice in Artificial Intelligence, Data Science and Machine Learning

Excellent! thank you! that's worked and has helped me make some other decisions too,. Very helpful, much appreciated! 

 

wini_R
Resolver IV
Resolver IV

Hi @Gimmerzz,

 

Assuming you have the following table named tabC:
Year Month Value
2023 Feb 75
2023 Jan 79
2023 Jan 86
2023 Jan 90
2023 Feb 99

You can try the fllowing measure to caclulate the average based on your requirements:

averageByMonth =
VAR _tab =
ADDCOLUMNS(
	SUMMARIZE(
		tabC, tabC[Year], tabC[Month])
	, "avg", CALCULATE(AVERAGE(tabC[Value]))
)
VAR _result = AVERAGEX(_tab, [avg])
RETURN _result

 

wini_R_0-1710003230479.png

 

Hi, thank you for taking time to look into and respond to me, I used another option as found it easier to understand/implement however I will review what you've shared again to see what I can learn from you. Thanks 😊 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

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! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.