Dynamic OVERALL AVERAGE of AVERAGEX Results

[rymerco]

I have a request that I cannot seem to solve and thought I would approach the wonderful PBI community.

GOAL: Isolate the overall historical average product of a series of values generated with a few 'X' measures to assign to a measure-filtered list of individuals. I want to take those isolated historical averages of the topN individuals across all stat categories and calculate the variance of current year's agents' stats to the aggregated averagex results per stat category.

WHAT I HAVE TRIED: I have tried to AVERAGE the results by assigning ties to an unrelated table to flatten and disregard 'X' measure association.  I have tried a calculated column, but the list of records in the required table is too large, so the demand on resources slows the results down to an unacceptable speed.

POSSIBLE FIXES???: SUMMARIZE, SUMMARIZECOLUMNS, JOIN

4 Tables

Table1 (MANY) = Has time stamped statistics on individuals

Table2 (MANY) = Has annual year-end time-stamped results from sales

Table3 (ONE) = Reference of Table2, creating a unique list of Individuals, then is uniquely indexed

Calendar (ONE) = Self-explanatory

The measures are filtered by a visual in PBI at Calendar[Date] of previous years' results to forecast current year event.

Measure that produces averagex results to assign to all individuals once calculated:

Pos10_stat_AVGX = 
CALCULATE(
averagex(Table1,Table1[StatAverage_BaseAVG]),
filter(Table3,Table3[Pos10]=1)
)

~ Averages the isolated results of each individual who finished in the top 10 of results in Table2 by stat category in Table1.  With Table3, I can create tables, charts or other visuals with the 'X' products of measures.

Breakdown:

Step 1

Table2[1-10_Pos] =

1-10_Pos = sumx(Table2, if( calculate( sum( Table2[Pos] ))<=10,1,0))

~ identifies each individual that was top 10 in all events, each year > Mapped to Calendar[Date]

Step 2

Table3[Pos10] = 

sumx( filter(Table3,Table2[1-10_Pos]>0), 1)

~ assigns a 1 to every event where the individual meets the conditions of the measure in Table2

Step 3

Table1[StatAverage_BaseAVG] = AVERAGE(Table1[STAT_VAL])

~ averages all of the values per row in the column on Table1 > Mapped to Calendar[Date]

--

Can anyone help me to generate a dynamic measure to calculate the aggregate average of the Pos10_stat_AVGX?

NOTE: I have attempted to add images and files but the browser I am using is giving me fits. I will attempt to add images and examples following the post.This is the end result summaryTable Mapping

[rymerco]

I have now included some samples for review.

Click on the link to my dropbox:

https://www.dropbox.com/s/bqew9gqh7d9c9qp/PBI_Forum_Example_OVERALL-AVERAGE-X.pbix?dl=0

Let me know if you have issues accessing the file or if my 3 GOALS noted in the textbox and in the first message, are not clear.

[Anonymous]

I have a similar requirement. Can you please share the solution or tips to achieve this ?

[Greg_Deckler]

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Need sample data that can be copied and pasted.

Also, I'm not sure I'm getting what you are going for here but I it seems similar to this post...This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

[rymerco]

Thanks Greg.

I will review and provide sample data.