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.
Hello,
I would like to calculate an annual average of values. However the calculation made by power BI with the quick measure average by year only count the distinct years where there is a value which is not the calculation I want as I need the annual average taking into account years empty.
Please see example below :
Annual average from 2008 (empty) to 2017 made manually (Total divided by 10 years) = 165 000/10 = 16 500 whereas Power BI calculates the average of values as to know 18 333 (corresponds to 165 000/9 years with a value)
Étiquettes de lignes | 2009 | 2010 | 2011 | 2012 | 2013 | 2014 | 2015 | 2016 | 2017 | Total général |
Asie Pacifique | 20000 | 10000 | 15000 | 20000 | 20000 | 20000 | 20000 | 20000 | 20000 | 165 000 |
In advance thank you for your help !
Cécile
Solved! Go to Solution.
You may try the measure below.
Measure = IF ( HASONEFILTER ( Table1[year] ), SUM ( Table1[value] ), DIVIDE ( SUM ( Table1[value] ), COUNTROWS ( ALL ( Table1[year] ) ) ) )
You may try the measure below.
Measure = IF ( HASONEFILTER ( Table1[year] ), SUM ( Table1[value] ), DIVIDE ( SUM ( Table1[value] ), COUNTROWS ( ALL ( Table1[year] ) ) ) )
Hello,
Thanks a lot for your precious help !
I just did a small change as I have filters in my model and changed the ALL in ALLSELECTED.
Have a great day !
Best regards
Cécile
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |