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

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

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

View solution in original post

14 REPLIES 14
lbendlin
Super User
Super User

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

 

 

Anonymous
Not applicable

Thank you but i would go with @amitchandak  solution.

fair enough - whatever works for you.

Greg_Deckler
Super User
Super User

@Anonymous - 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!!!
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...
amitchandak
Super User
Super User

@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
Not applicable

@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
Super User
Super User

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
Not applicable

@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 ?  🙂 

Anonymous
Not applicable

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

Anonymous
Not applicable

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

@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
Not applicable

Thanks alot.

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.