- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# Calculating average of measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-25-2017 11:31 PM

I want to consider the average scan rates of each of the 4 employees for a given week and then return the Max and Min values for a week.

I have the following measures: (and many many more that' I've tried!)

- Tot Pages Scanned:=sum(PagesTime[Pages])
- Tot Scan Hrs:=sum(PagesTime[Hours Scan])
- Emp Scan Rate:=DIVIDE([Tot Pages Scanned],[Tot Scan Hrs])
- Avg Wkly Scan Rate:=averagex(PagesTime,DIVIDE(PagesTime[Pages],PagesTime[Hours Scan]))

For the Max, I tried

WeeklyMax:=CALCULATE(MaxX(VALUES(PagesTime[Iso Week]), [Avg PPH]),all(PagesTime[Employee]))

I even thought I had found the brass ring with the solution explained here and here. But neither seemed to work for me.

I can’t for the life of me figure out how to take the Max/Min of a measure in the context of the week.

Anybody interested in pointing out what I’m missing?

I don't see a means of attaching my file. But hopefully the screen shots will suffice.

Thanks in advance…

Rich P

Solved! Go to Solution.

Accepted Solutions

## Re: Calculating average of measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2017 11:23 PM

OK so I had a crack at this and I got it working with the following:

Avg Scan Rate = CALCULATE(AVERAGEX(Data,DIVIDE(Data[Scans],Data[Hours])),ALLEXCEPT(Data,Data[Week],Data[Employee]))

MAXX = CALCULATE(MAXX(ALL(Data[Employee]),[Avg Scan Rate]))

The answer for me was in one of the posts you linked... I think the ALLEXCEPT in the scan rate measure is crucial for evaluating the context correctly.

I haven't completely unpicked this myself so I can't give a proper explanation as to why it works sorry. There is probably a more elegant way to do this but hey worked for me

I hope this helps!

All Replies

## Re: Calculating average of measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2017 12:12 AM

Your DAX is good, but it looks like you are not thinking about the problem in the terms of filter context. As I understand, you want a line that gives the overall max over all weeks in the chart, right? I suggest you go through the following process.

1. set up your data as a matrix or table (so you can see the numbers). Put the weeks on rows and the measure that varies by week next on rows or columns (depending on if you have a table or matrix respectively)

2. write your formula that is supposed to give you a straight line and add it to the visual.

If the measure is not giving you the same number all the way down the page, ask yourself "why not" while thinking about "filter context". The rows in the visuals will filter your datamodel. Your job is to write a formula that ignores the weeks on rows and gives you the avearge over ALL periods in the visual (that was a hint :-) )

## Re: Calculating average of measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2017 08:12 AM

Matt - It's a pleasure! Yours was the first book on DAX that I bought!

Actually, I am trying to find the Max & Min in each week. And as you wrote in your chapter 6, I even tried a couple of approaches:

a) using a measure to calculate the Sum of Pages and another to calc the Sum of Scan Hours and another to calc the Average Scans/Hr

b) using a calculated column

But no matter how I try to write the Max measure, It always returns something that is outside the row context of Week Number.

Any suggestions? Or is there any way to attach my file? This is my first post, so I'm not sure if you have to be more active before that feature is unlocked?

Thanks for the reply.

Rich

p.s. Very subtle hint {ALL()} LOL

## Re: Calculating average of measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2017 04:33 PM

you can load a file to dropbox or onedrive and post the link. If you want to add back the week filter for the current week, simply add VALUES(Cal[Week]) as an extra filter argument to calculate.

## Re: Calculating average of measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2017 09:01 PM

I have tried a simplified model to start from scratch and thought I had it, but it just isn't returning the values I want.

Per your suggestion, I have included a link to the example file. I'm hoping you can take a few minutes to look. Or anyone else, for that matter.

Thanks again.

Rich

## Re: Calculating average of measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2017 11:23 PM

OK so I had a crack at this and I got it working with the following:

Avg Scan Rate = CALCULATE(AVERAGEX(Data,DIVIDE(Data[Scans],Data[Hours])),ALLEXCEPT(Data,Data[Week],Data[Employee]))

MAXX = CALCULATE(MAXX(ALL(Data[Employee]),[Avg Scan Rate]))

The answer for me was in one of the posts you linked... I think the ALLEXCEPT in the scan rate measure is crucial for evaluating the context correctly.

I haven't completely unpicked this myself so I can't give a proper explanation as to why it works sorry. There is probably a more elegant way to do this but hey worked for me

I hope this helps!

## Re: Calculating average of measures

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

06-26-2017 11:37 PM

DearWatson - THANK YOU!!! This is perfect, Exactly what i was looking for.

Not sure how you came up with that, but it certainly works.

I'll have to go over it a few times to understand what it is doing, but it will be worth it.

Thanks for taking the time to resolve this for me.

Best Regards,

Rich P