Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Help with Measure Issue

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!!

4 REPLIES 4
Anonymous
Not applicable

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.

Anonymous
Not applicable

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!!

Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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!

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.