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.
I have a data set of 6 years of data.
I want to create a column to calculate the average of "% of EOM" for each of the 6 years on that day.
e.g. for 31st December it would calc the average of % of EOM across these dates 31/12/18, 31/12/17, 31/12/16, 31/12/15. 31/12/14, 31/12/13
I've been scratching my head and can't work it out - any help much appreciated!
Solved! Go to Solution.
Hi @smsat ,
AVG_%_of_EOM = CALCULATE ( AVERAGE ( Test_2[% of EOM] ), FILTER ( ALLSELECTED ( Test_2 ), FORMAT ( Test_2[Date], "MMM dd" ) = FORMAT ( MAX ( Test_2[Date] ), "MMM dd" ) ) )
Best regards,
Yuliana Gu
Hi @smsat ,
Please try this measure:
Average = CALCULATE ( AVERAGE ( Table[% of EOM] ), ALLEXCEPT ( Table, Table[Date].MONTH, Table[Date].DAY ) )
Best regards,
Yuliana Gu
Thanks very much for the reply @v-yulgu-msft
That hasn't quite worked. It's produced the same % in every row, I can't quite work out what it has averaged to get to 55.79%.
Ideally what I'm looking for it to do is for each row to average all other years on that date, e.g. on any row with date 31 December it will average
31/12/18 102.6%
31/12/17 62.8%
31/12/16 66.9%
31/12/15 73%
31/12/14 70%
31/12/13 82.2%
and the "This Date Average" column for every 31st December will show = 76.25%
This value will be different for 30 December, 29 December, etc.
Hi @smsat ,
AVG_%_of_EOM = CALCULATE ( AVERAGE ( Test_2[% of EOM] ), FILTER ( ALLSELECTED ( Test_2 ), FORMAT ( Test_2[Date], "MMM dd" ) = FORMAT ( MAX ( Test_2[Date] ), "MMM dd" ) ) )
Best regards,
Yuliana Gu
Excellent, thank you very much indeed.
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |