PowerBi Averaging issue

[Gimmerzz]

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 

[wini_R · Accepted solution]

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

[Sahir_Maharaj · Accepted solution]

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

[Gimmerzz]

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

[wini_R · Accepted solution]

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

[Gimmerzz]

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 😊