DAX Formula For Averages per week static per person

[KarenFingerhut]

Hi there

I'm trying to calulcate an average figure (simple usually). However this figure needs to be based on a calculation of overall per year then drill to month and then drill to week. So against every person for those drill periods I want the average to be the same against each person. Then if the average changes the following week I want the new average figure to be the same for every person. I can then create a conditional format against it. 

The DAX I have created works on a card visual level when I select a particular week, but as soon as I drop it in to my table where all the user names and years/months/weeks are listed the figure changes

E.G Week 45 card visual saya 29.57 but when I add it to the table it breaks down like this (column highlighted in yellow)

In that table are weeks for the last 12 months. As you can see the total for the column is correct at 29.57 but I just cant get it to work against each individual person.

My DAX is 

# Dynamic Average = CALCULATE(AVERAGEX(FILTER(ALLSELECTED(Activity), Activity[fiscal_week_processing_date] <= MAX(Activity[fiscal_week_processing_date])),SUM(Activity[Cases worked])/SUM(Activity[Days worked])))

Hope this makes sense!

Can someone please help.

Thank you

Kind regards

Karen

[lbendlin]

You are using CALCULATE without a filter context modifier.  Is that intended?

Please provide sanitized sample data that fully covers your issue. Paste the data into a table in your post or use one of the file services. 

[KarenFingerhut · Accepted solution]

HI there

I managed to resolve the issue by using this

Dax 1

CALCULATE(DIVIDE(SUM(Activity[Cases worked]),SUM(Activity[Days worked])))

Dax 2

CALCULATE ([# Average Cases Worked],ALLSELECTED(Activity))

What these do is,  Dax 2 enables the card visual 'Overall Daily Average' to change when a fiscal week or month is selected and then Dax 1 is used against each indivisual to see the % difference between their average and the overall average

Thanks for getting back to me

Kind regards

Karen