cancel
Showing results for
Did you mean:
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.

 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
1 ACCEPTED SOLUTION
Super User IV

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

Proud to be a Super User!

14 REPLIES 14
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.

Helper V

Thank you but i would go with @amitchandak  solution.

Super User III

fair enough - whatever works for you.

Super User IV

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

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

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

Proud to be a Super User!

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])

Proud to be a Super User!

Helper V

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

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?

Helper V

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.

Super User III

your store is open 24x7 ?  🙂

Helper V

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

Super User III

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

Helper V

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

Super User IV

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

Proud to be a Super User!

Helper V

Thanks alot.

Announcements

Happy New Year from Power BI

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