cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Calculating average of measures

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.

 

RawData.jpgDesiredOutcome.jpg

 

PwrPivScreen.jpg

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Responsive Resident
Responsive Resident

Re: Calculating average of measures

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!

 

Capture.PNG

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

6 REPLIES 6
Highlighted

Re: Calculating average of measures

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 🙂   )



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Helper II
Helper II

Re: Calculating average of measures

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

Highlighted

Re: Calculating average of measures

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.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
Highlighted
Helper II
Helper II

Re: Calculating average of measures

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.

MyDataScreenshot.jpg

 

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

Highlighted
Responsive Resident
Responsive Resident

Re: Calculating average of measures

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!

 

Capture.PNG

 

 

 

"The commonality between science and art is in trying to see profoundly - to develop strategies of seeing and showing." - Edward Tufte

View solution in original post

Highlighted
Helper II
Helper II

Re: Calculating average of measures

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

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors