Dax Measure to Average Two Measures

[bravo1563]

Hello,I have a PowerPivot dataset for Football stats in which I have two measures created “RecYdsDomPct” and “RecTDDomPct” that I want to average together. The average of these should be 35.17%. However when I remove any of my other fields (day and game id) the percentages do not tie back to 35.17%. I’ve tried a Sumx formula but did not get the right result.
Any help would be great!
Link to file…https://www.dropbox.com/s/q6duddema3o0izj/CFB PlayerGameStats.xlsx?
I’ve attached the way it should really calculate so you can see what I’m trying to get.
Thanks!
[h=6]Attachments:[/h]

• cfb_schools_louisville_2013_gamelog__offense-1.xlsx

 

[Matt Allington]

You are correct that you need to use an Iterator (like SUMX or AVERAGEX). The problem you are experiencing is due to the way Power Pivot works. Power Pivot first applies filters from the pivot table (initial filter context), then does the calculation. Each row in your pivot table is filtered for one game, and hence the % calculations are done on the stats for that single game. When you get to the grand total row, the data from all games is combined before doing the calculation and hence instead of getting the result of each game calculated individually and then averaged, you get all the stats from all games aggregated and then averaged as if it were a single game.

When ever you see this, you need to think about how you can simulate what the pivot table is doing. From what I can see, the pivot table is iterating over each game, one at a time to do a game by game calculation. To simulate that using an iterator, you need to produce a temp table that will give you the list of all the games. You can use Values for that. Values will pick up the list of games that are in your current filter context (so no need for allselected).

So this should work

Average Works:=AVERAGEX(values(tblGames[GameID]),[mRecDomPct])