Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
I want to calculate the Monthly Average cost per year.
Currently I have the below :
Monthly Average Cost = Divide(sumx('Table','Table[Cost]), distinctcount('Calendar'[Month])).
This measure is working. I have however a visual on that page and if you select a speific month, the Monthly Average Cost provides the total cost of that month and divides it by the months in that year. What I would like to see is the Monthly Average of that selected year(s).
@YBZ,
Try these measures:
Total Cost = SUM ( 'Table'[Cost] )
Monthly Average Cost =
CALCULATE (
AVERAGEX ( VALUES ( Calendar[Month] ), [Total Cost] ),
// clear Calendar filter context
ALL ( Calendar ),
// keep the Year filter context from the slicer
VALUES ( Calendar[Year] )
)
Proud to be a Super User!
Thanks, but that did not work
It gives me 0 in the table and when I click on a specif month, it gives me strange values 🙂
User | Count |
---|---|
98 | |
90 | |
82 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
67 |