Forum Discussion

Gimmerzz's avatar
Gimmerzz
Advocate I
2 years ago
Solved

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...
Need Help
  • wini_R's avatar
    wini_R
    2 years ago

    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's avatar
    Sahir_Maharaj
    2 years ago

    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])
wini_R's avatar
wini_R
Solution Supplier
2 years ago

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's avatar
    Gimmerzz
    Advocate I
    2 years ago

    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 😊