cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
almafater2
Helper I
Helper I

Average of values on specific dates only

 

Hey cummunity,

 

I am trying to calculate the average of GAV, but it has to count only the values of GAV on the last day of every month in the selected range (see picture below). The rusult I am getting is slightly of.

Screenshot 2021-06-27 121330.png


According to my calaculations it should be  299 174 307,14 for year 2020 and 326 112 602,05 for 2021. The formula I am using is:
 

Average GAV =

VAR MaxMonth = CALCULATE(MAX(Dates[MonthNum]), ALL(Dates), Dates[CurMonthOffset] = 0)

RETURN

CALCULATE(AVERAGEX( VALUES(Dates[EndOfMonth]), [GAV] ), Dates[MonthNum] <= MaxMonth, FILTER(Dates, ALLSELECTED(Dates[Year])), ALL('IslaiduLentele Map'))

 

 Do you have any suggestions on how I could change it ?

 

I am ataching an example PBI file and my gratitude :

https://www.dropbox.com/s/52co15sbnqnk0ka/Example4.pbix?dl=0

1 ACCEPTED SOLUTION

OK @almafater2 

but when year=2020, avg should be 24931192.2616667 (Not 27 197 664,29)  if your expected data could be accurate, then we can work on it quickly.🤣  FYI: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1579748#M6420...

-

use the measure bellow,

 

Measure_average =
VAR _count =
    CALCULATE ( DISTINCTCOUNT ( Dates[EndOfMonth] ), ALLSELECTED ( Dates ) )
VAR _sum =
    SUMX ( SUMMARIZE ( Dates, Dates[EndOfMonth], "s1", [GAV] ), [s1] )
RETURN
    _sum / _count

 

result:

vxiaotang_0-1625120432135.pngvxiaotang_1-1625120445074.png

 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
v-xiaotang
Community Support
Community Support

Hi @almafater2 

The description of the problem is not very clear. 

year=2021,  AVG=326 112 602,05;

year=2020,  AVG=299 174 307,14; 

but how is this calculated? please let me know whose average you want to calculate.

the average value of the data in the picture is... Obviously it's not the result you want. so please put the true picture with data where your average comes from.

vxiaotang_0-1624945670201.png

FYI:

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1579748#M6420...

 

 

 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution✔️ to help the other members find it more quickly.

Thank you for your answer and patience 🙂 

The range described in the picture is correct, what is wrong is the expected values I sent you. 

year=2021,  AVG = 27 176 050,17;

year=2020,  AVG = 27 197 664,29;

Sory for the mistake.



OK @almafater2 

but when year=2020, avg should be 24931192.2616667 (Not 27 197 664,29)  if your expected data could be accurate, then we can work on it quickly.🤣  FYI: https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1579748#M6420...

-

use the measure bellow,

 

Measure_average =
VAR _count =
    CALCULATE ( DISTINCTCOUNT ( Dates[EndOfMonth] ), ALLSELECTED ( Dates ) )
VAR _sum =
    SUMX ( SUMMARIZE ( Dates, Dates[EndOfMonth], "s1", [GAV] ), [s1] )
RETURN
    _sum / _count

 

result:

vxiaotang_0-1625120432135.pngvxiaotang_1-1625120445074.png

 

Best Regards,

Community Support Team _ Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors