Forum Discussion

ianallen13's avatar
ianallen13
Helper I
5 years ago
Solved

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 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.  
 
 
 
 
 
Need Help
Show and Tell
Tips and Tricks
  • aj1973's avatar
    aj1973
    5 years ago

    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.

     

11 Replies

  • aj1973's avatar
    aj1973
    Community Champion
    5 years ago

    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.

     

    • ianallen13's avatar
      ianallen13
      Helper I
      5 years ago

      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?  

      • aj1973's avatar
        aj1973
        Community Champion
        5 years ago

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

         

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    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.

    • ianallen13's avatar
      ianallen13
      Helper I
      5 years ago

      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?