Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I created a measure to calculate the total number of unique Sundays within a dataset.
"CountSundays = calculate (distinctcount('visits ahop'[date entered]))"
I also created a measure to calculate the total quantity per hour.
"HourVisits = calculate(count('Visits AHOP'[hour]),'Visits AHOP'[QUANTITY])"
I created a measure to calculate the average visits per hour.
"VisitsPerHour = divide([HourVisits],[countsundays],0)"
My "VisitsPerHour" measure is causing problems though. Instead of using the *total* number of unique Sundays across every hour, it finds the number of unique Sundays for *each* Hour, meaning that both the denominator and numerator of the formula changes across each hour.
How do I make it so each hour is divided by the same number of Sundays?
Thanks!!
I am struggling with getting the measures I've created to operate the way I need them to.
Below is a brief example of the data I'm working with:
Date Entered Time Entered Quantity Hour #Sundays
54
9/10/2017 11:10 AM 1 11
3/18/2018 1:02 PM 1 13
8/6/2018 10:57 AM 1 10
And so on.
This formula: "HourVisits = calculate(count('Visits AHOP'[hour]),'Visits AHOP'[QUANTITY])" can successfully summarize the quantity for each hour block.
This formula: "CountSundays = calculate (distinctcount('visits ahop'[date entered]))" counts the total number of distinct days.
(The actual number of unique days found in the database is 54)
This formula: "VisitsPerHour = divide([HourVisits],[countsundays],0)" divides the quantity for each hour by the number of days.
What I want is for "VisitsPerHour" to divide each hour's specific quantity by the **total** number of distinct days found in my database.
What I'm getting is "VisitsPerHour" dividing each hour's specific quantity by the specific number of distinct days that had a transaction occur during that hour block.
Here is a replication of the datatable as it's showing now (sorry I don't know how to upload a screenshot of the datatable)
HOUR QUANTITY COUNTSUNDAYS VISITSPERHOUR
9 8 5 1.6
10 98 50 1.96
11 125 51 2.45
12 122 50 2.44
So for the 9 o'clock hour, because only 5 days actually had data in the 9 o'clock hour, it's dividing the quantity of 8 by the count of 5. What I want is for the quantity of each hour to be divided by 54 (the actual total # of unique days), regardless of how many days actually had data occur in that hour.
I created a measure to calculate the total number of unique Sundays within a dataset.
"CountSundays = calculate (distinctcount('visits ahop'[date entered]))"
I also created a measure to calculate the total quantity per hour.
"HourVisits = calculate(count('Visits AHOP'[hour]),'Visits AHOP'[QUANTITY])"
I created a measure to calculate the average visits per hour.
"VisitsPerHour = divide([HourVisits],[countsundays],0)"
My "VisitsPerHour" measure is causing problems though. Instead of using the *total* number of unique Sundays across every hour, it finds the number of unique Sundays for *each* Hour, meaning that both the denominator and numerator of the formula changes across each hour.
How do I make it so each hour is divided by the same number of Sundays?
Thanks!!
Very difficult to understand what is going on here without sample data. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Thanks for the response. Below is a brief example of the data I'm working with:
Date Entered Time Entered Quantity Hour #Sundays
54
9/10/2017 11:10 AM 1 11
3/18/2018 1:02 PM 1 13
8/6/2018 10:57 AM 1 10
And so on.
This formula: "HourVisits = calculate(count('Visits AHOP'[hour]),'Visits AHOP'[QUANTITY])" can successfully summarize the quantity for each hour block.
This formula: "CountSundays = calculate (distinctcount('visits ahop'[date entered]))" counts the total number of distinct days.
(The actual number of unique days found in the database is 54)
This formula: "VisitsPerHour = divide([HourVisits],[countsundays],0)" divides the quantity for each hour by the number of days.
What I want is for "VisitsPerHour" to divide each hour's quantity by the total number of distinct days found in my database.
What I'm getting is "VisitsPerHour" dividing each hour's quantity by the number of distinct days found in that hour block.
Here is a replication of the datatable as it's showing now (sorry I don't know how to upload a screenshot of the datatable)
HOUR QUANTITY COUNTSUNDAYS VISITSPERHOUR
9 8 5 1.6
10 98 50 1.96
11 125 51 2.45
12 122 50 2.44
So for the 9 o'clock hour, because only 5 days actually had data in the 9 o'clock hour, it's dividing the quantity of 8 by the count of 5. What I want is for the quantity of each hour to be divided by 54 (the actual total # of unique days), regardless of how many days actually had data occur in that hour.
I really hope that helps, I'm trying to be accountable towards that Community Blog you sent me, but unfortunately I'm not sure how to post my sample data in a more efficient way.
Thanks Greg!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
117 | |
101 | |
71 | |
61 |