I am trying to create a single measure that will calculate a date average, depending on the drill down on a time barchart.
The bar chart has 5 time pieces in the hierarchy. Year - Quarter - Month - Week -Day
As I drill through the bar chart, I want a measure that will dynamically calculate the Yearly Avg - Quarterly Avg - Monthly Avg, Weekly Avg, Daily Avg. For example, lets say the barchart is on the Year Level and displaying 4 bars representing 2016-2019. The vale of the measure would be COUNT(UnitID) / 4 because there are 4 bars currently displayed on the X axis.
Lets drill into 2018 to the month level. There are 12 bars representing Jan-Dec. The value of the measure would be COUNT(2018 UnitIDs) / 12 because there are 12 bars currently displayed on the X axis.
Lets drill into 2019 to the month level. There are only 10 bars representing Jan-Oct. The value of the measure would be COUNT(2019 UnitIDs) / 10 because there are only 10 bars currently displayed on the X axis.
Finally, lets not drill down, but just expand the hierarchy from year to month. We go from the yearly view showing 4 bars to the monthly view showing 12. But the Jan total is the sum of 2016Jan + 2017Jan + 2018Jan + 2019Jan. The measure needs to interpret this as COUNT(All UnitIDs) / 12 because there are 12 months currently displayed on the X Axis.
I'm basically trying to figure out how to create a measure that counts whatever number of values are on the X axis at any given time. Thank you all for your advice and feedback. I'm really looking forward to testing your responses and seeing if they work!
