Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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!
Hi, @Keith3618
it would be really helpfull if you also could provide some mockups and examples with values, to get an idea of you desired output
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
12 | |
11 | |
7 | |
6 |