Reply
Regular Visitor
Posts: 20
Registered: ‎06-22-2017
Accepted Solution

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


Accepted Solutions
Highlighted
Member
Posts: 81
Registered: ‎07-13-2015

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 Smiley Happy

 

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


All Replies
Super User
Posts: 1,627
Registered: ‎07-03-2015

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.
Regular Visitor
Posts: 20
Registered: ‎06-22-2017

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

Super User
Posts: 1,627
Registered: ‎07-03-2015

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.
Regular Visitor
Posts: 20
Registered: ‎06-22-2017

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
Member
Posts: 81
Registered: ‎07-13-2015

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 Smiley Happy

 

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
Regular Visitor
Posts: 20
Registered: ‎06-22-2017

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