cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rymerco Frequent Visitor
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.ScreenHunter 518.jpgThis is the end result summaryScreenHunter 517.jpgTable Mapping

4 REPLIES 4
Super User
Super User

Re: Dynamic OVERALL AVERAGE of AVERAGEX Results

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


I have book! Learn Power BI from Packt


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

rymerco Frequent Visitor
Frequent Visitor

Re: Dynamic OVERALL AVERAGE of AVERAGEX Results

Thanks Greg.

 

I will review and provide sample data.

rymerco Frequent Visitor
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.

krishnasnair Frequent Visitor
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 ?

Helpful resources

Announcements
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

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

November 2019 Community Highlights

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

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)