CALCULATE AVERAGE OF MONTHLY SUM

[Anonymous]

Hi All,

I want to calculate the average of monthly percentages rather than summing up total values and then calculating the percentage

For e.g

  A       B     MONTH

50	5	JAN
60	10	JAN
60	10	JAN
50	10	FEB
70	5	FEB
80	5	FEB

In the above table, Power bi is calulating sum(A)/sum(B) i.e. 370/45 = 8.22

I actually want to first sum and divide monthly values and then calculate the average

Desired result : (((50+60+60)/(5+10+10))+((50+70+80)/(10+5+5)))/2 (as this is just 2 months data)

                         = (6.8 + 10)/2

                         = 8.4 (instead of 8.22)

How can I achieve this in Power BI? Please help

[Anonymous · Accepted solution]

Got the solution by referring to https://community.powerbi.com/t5/DAX-Commands-and-Tips/average-of-yearly-percent/m-p/823197

AVERAGEX(VALUES('TIME'[MONTH-YY]),CALCULATE(DIVIDE([A],[B]),ALLSELECTED('TIME'[PK_Date])))

Thanks All

[amitchandak]

Try like

measure =
AverageX(summarize(table,table[month],"_AB",divide(sum(Table[A]),sum(Table[B]))),_AB)

refer:

https://community.powerbi.com/t5/Desktop/SUM-of-AVERAGE/td-p/197013

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin