cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Calculating rate of an event/day when event doesn't happen every day

Hey guys,  If I use the following calculation:

 

Event rate = COUNT[Events]/DISTINCTCOUNT[Date]

 

I find that the numerator is lower by the number of days an event didn't happen.  For example, if I plot Event rate vs. time (by month) my rate will be artificially high if there are dates no event occurred because it will not count these dates.

 

Any suggestions?  I've also tried using DATEDIFF(MIN(Date), MAX(Date),DAYS) which gets me closer but will also be artificially high if an event doesn't happen on the first or last date.

 

Thanks!

1 ACCEPTED SOLUTION
Frequent Visitor

I figured out a solution that is working.  It required the use of a date table and creating a relationship in the model.  Then I could use as my denomentator Countrows('DateTable'[Day])

 

View solution in original post

4 REPLIES 4
Frequent Visitor

I figured out a solution that is working.  It required the use of a date table and creating a relationship in the model.  Then I could use as my denomentator Countrows('DateTable'[Day])

 

View solution in original post

Frequent Visitor

Thanks for the suggestion!  I tried that but it still does not account for days that no event occured.  I attached a test data set.  When I select a particular week, only 5 distinct day counts even though there was 7 days.  I want to get the 3.71events/day vs. the 5.20events/day (basically dividing the number of events by 7 instead of 5)

pagrosse_0-1605546842001.png

https://1drv.ms/u/s!AmSn6NUGP_obgx6AgwVe8naxVkDc?e=EQVesd 

Hi @pagrosse ,

Based on your sample data and description, what cnfused me is that since there are 7 days in a week, just divide by 7 directly as you write in the formula. Why do we need to get 7 by calculation as the denominator? 

Maybe you can consider sharing more details about this issue for further discussion.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Super User IV
Super User IV

@pagrosse , Try a measure like

Event rate = divide(COUNT(Table[Events]),calculate(DISTINCTCOUNT(Table[Date]), filter(Table, not(isblank(Table[Events])))))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors