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 counts of items per day. I want to sum the items to get a total for each day, then show a time series chart showing the daily AVERAGE across time - no matter what time period is on the drill down.
I use a date table for the date hierarchy.
The average daily sum is about 30-40, but when viewing year, it shows the total of all items across every day in the year - about 700.
For quarter > month > week, naturally the number drops until once you've drilled down to day you get the correct daily sum.
When viewing daily (drill down in the chart all the way to by day) you see the daily sums - about 30-40.
When drilled 'up' at quarter I want to still see the daily average, but actually I see the quarterley sum - about 100.
My data is structured like this. Blocked out in red is my item count - for a particular day:
This all happens when I click the chart drill-down button:
Hoping you can help. 🙂
Hi @Anonymous ,
Do you want to show the average values whenever drill down the date hierarchy? Do you try the average in fields?
If I misunderstand, please share the expected result to us. We will understand more clearly.
Yes I'd like to see the average daily value - however this needs to be the sum of the different value types for each day.
In my data screen shot above you'll see each day has multiple data entries for different locations. I need these to be summed for the day, but averaged to give the daily average (whether looking yearly, monthly, weekly, etc).
I have tried using average (as per your suggestion) it averaged all the values for that day, then averaged all the days across the time period.
My data values typically range between 0 and 20 with a lot of zeros - so the average comes out at below zero. But if you sum all records for the day first, then take the daily average across time it should be around 30-40.
So I assumed creating a measure like this
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 |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |