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
ianallen13
Helper I
Helper I

Help with Dax Formula - Average Hours Per Week Visual Assistance

Hello, 

 

I have a visual that shows the average hours worked on certain services per week.  On the whole, this visual / formula works exactly as I like it to - Except - what I would like is when all sessions are selected, it seems to just divide the total hours worked/by the total number of weeks, which for all-sessions - is not correct.  

 

Anyone have any ideas?

 

Here is the formula: 

Hours average per Week =
AVERAGEX(KEEPFILTERS(VALUES('Escalations_Cumulative'[Week])),CALCULATE(SUM(Escalations_Cumulative[Hours]))) 
 
Pic 1 is when it is filtered by session - works fine - which it should. 
Pic 1.jpg
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Pic 2 is where I am trying to figure out how to get around it.  For the total number of weeks starting in the July 2020 session until now, after doing the math manually it is like 30+ hours on average per week.  
 
Pic 2.jpg
 
 
 
 
1 ACCEPTED SOLUTION

Power bi reports are treated case by case, so maybe a sample Pbix would be a better idea so i can work on it and help you understand.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

View solution in original post

11 REPLIES 11
v-angzheng-msft
Community Support
Community Support

Hi, @ianallen13 

May I ask whether your problem has been solved, if so, can you consider sharing your solution,and then mark your post as Answered since it is working now? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Hello - yes - another person was able to help build a separate table within the file and that is how they were able to work around it.  Let me know if that helps?

ianallen13
Helper I
Helper I

Message sent. 

aj1973
Community Champion
Community Champion

Hi @ianallen13 

When you check box "Select all" it is like you are including values from blank rows in your model. it is better not to use select all and keep the boxes unchecked( it is doing the same thing as you check box all of them.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

But I'd like it to show 'all weeks' from 'all sessions' as well to display the overall average from the time that we started this, but is there a way to write the formula to show that when they are all checked?  

Embed your formula into a CALCULATE function and filter out the blank rows.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

I'm still learning - how would that look? 

@ianallen13 

Here is a quick example:

aj1973_0-1623162138614.png

As you can see the 74 Blank rows are not included in the calculation of number of days.

 

do the same for your avarage formula where ever is neccessary.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

I do not have any blank lines, as when each session is selected by itself - it works correctly - but when all sessions are selected (along with all weeks), I want it to show the average of all sessions.  

 

Sorry if I am not understanding correctly? 

Power bi reports are treated case by case, so maybe a sample Pbix would be a better idea so i can work on it and help you understand.

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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.

Top Solution Authors
Top Kudoed Authors