Find the duration in hours by group

[Anonymous]

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.

Date	Name	Sold
7/17/2020 12:04:46 PM	Justin	10
7/12/2020 9:01:06 AM	Ellen	0
7/20/2020 10:04:26 AM	Justin	9
7/17/2020 6:04:16 AM	Dave	2
7/15/2020 8:04:46 PM	Ellen	0
7/17/2020 11:14:03 AM	Dave	100

[lbendlin]

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.

[Anonymous]

Thank you but i would go with @amitchandak  solution.

[lbendlin]

fair enough - whatever works for you.

[Greg_Deckler]

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

[amitchandak]

@Anonymous , 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-Difference-Across/ba-p/934397#M451

[Anonymous]

@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.

[lbendlin]

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?

[Anonymous]

@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.

[lbendlin]

your store is open 24x7 ?  🙂 

[Anonymous]

@lbendlin  

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

[lbendlin]

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

[Anonymous]

@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.

[amitchandak · Accepted solution]

@Anonymous , 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.

[Anonymous]

Thanks alot.