cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper V
Helper V

Find the duration in hours by group

I want to create a measure that calculates duration of hours between the Min and Max date by Name then divides by the sum of Sold column for each group in order to find the Avgtime. Please, bear in mind that some of the result might throw an error or undefined for a Name like "Ellen" because if we didvide anything by 0, it is undefined. How do i go about this in DAX. I know how to do it in power query but i don't want my entire data turned to an aggregated data. Thanks.

 

 

 

DateNameSold
7/17/2020 12:04:46 PMJustin 10
7/12/2020 9:01:06 AMEllen0
7/20/2020 10:04:26 AMJustin9
7/17/2020 6:04:16 AMDave2
7/15/2020 8:04:46 PMEllen0
7/17/2020 11:14:03 AMDave100
1 ACCEPTED SOLUTION

@Omonmaxi , In the first m1, I am taking no action when denomintor is 0 . So that is become 0 or null.

 

In second M1, 

M1= divide(datediff(Min(Table[Date]),Max(Table[date]),HOUR),if(sum(Table[Sold])<>0,sum(Table[Sold]),1))

I am making denomintor as 1 when it 0 so that Hours get considered. As if is used and row context will play a role, I also suggested 3rd formula which taked care of row context. 

 

As the first has no impact of row context it is not used in 3rd formula. Hope this will help.



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!

View solution in original post

14 REPLIES 14
Super User III
Super User III

This is how I would approach it. I would ignore the first sold quantity and only count the items that were sold after that.

The measure has to be cast as decimal, and the Name has to be shown including items that have no data.

 

lbendlin_1-1595710656713.png

 

 

Thank you but i would go with @amitchandak  solution.

fair enough - whatever works for you.

Super User IV
Super User IV

@Omonmaxi - If you use the DIVIDE function, you can return an alternate value for divide by zero erros.


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Super User IV
Super User IV

@Omonmaxi , Try like

m1 =divide(datediff(Min(Table[Date])Max(Table[date]),HOUR),sum(Table[Sold]))

M1= divide(datediff(Min(Table[Date])Max(Table[date]),HOUR),if(sum(Table[Sold])<>0,sum(Table[Sold]),1))

 

And force row context. of name

New M1= averageX(Values(Table[Name]),[M1])

 

refer :https://community.powerbi.com/t5/Community-Blog/Decoding-Direct-Query-in-Power-BI-Part-2-Date-Differ...



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!

@amitchandak 

 

Thanks for your response. Is the first m1 a mistake? the reason i ask is because i do not see where you used it in M1 and New M1. Thanks.

Super User III
Super User III

No, Ellen will not throw an error because you are dividing the # of items sold by the number of hours, not the other way round.

 

You want the average of items sold per person and hour, right?

@lbendlin 

 

Actually, the "Name" field is a Product field, I only named it as name just to show that there are different groups in my data. So in actual sense, Ellen =  Sugar, Justin = Ice cream and Dave = Water etc. I have multiple rows for each of this products, i just decided to limits the data to 6 rows. So, I want to find the average time taken (hrs) to sell 1 item of Sugar, Ice Cream and Water etc.

To answer your question, what i want to know is the Avg time in hrs it takes to sell 1 item.

your store is open 24x7 ?  🙂 

@lbendlin  

Yup, it is. Do you see why I'm calculating it like this now or do you think I'm still wrong?

"wrong"  is a strong term. Let's go with "unique".  Did @amitchandak 's reply get you what you need?

@lbendlin  I want your opinion, that's why i asked. If it were you, how would you calculate it? To answer your question if  @amitchandak  code solved my problem, I need more clarification from him. Thanks.

@Omonmaxi , In the first m1, I am taking no action when denomintor is 0 . So that is become 0 or null.

 

In second M1, 

M1= divide(datediff(Min(Table[Date]),Max(Table[date]),HOUR),if(sum(Table[Sold])<>0,sum(Table[Sold]),1))

I am making denomintor as 1 when it 0 so that Hours get considered. As if is used and row context will play a role, I also suggested 3rd formula which taked care of row context. 

 

As the first has no impact of row context it is not used in 3rd formula. Hope this will help.



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!

View solution in original post

Thanks alot.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors