Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
Solved! Go to Solution.
OK @Anonymous
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#M642005
-
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:
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.
Hi @Anonymous
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.
FYI:
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 @Anonymous
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#M642005
-
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:
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |