cancel
Showing results for
Did you mean:
Frequent Visitor

## Dynamic OVERALL AVERAGE of AVERAGEX Results

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

4 REPLIES 4
Super User

## Re: Dynamic OVERALL AVERAGE of AVERAGEX Results

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

### I have book! Learn Power BI from Packt

Proud to be a Datanaut!

Frequent Visitor

## Re: Dynamic OVERALL AVERAGE of AVERAGEX Results

Thanks Greg.

I will review and provide sample data.

Frequent Visitor

## Re: Dynamic OVERALL AVERAGE of AVERAGEX Results

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.

Frequent Visitor

## Re: Dynamic OVERALL AVERAGE of AVERAGEX Results

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

Announcements

#### New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

#### November 2019 Community Highlights

Get an overview of the events and great community content from November.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)