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 thetopNindividuals across all stat categories and calculate the variance of current year's agents' stats to the aggregatedaveragex 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
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.
Measurethat producesaveragexresults to assign to all individuals once calculated:
~ 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.