Getting the average of data in a table, filtered 15 records

[Anonymous]

Hi, I have a table that's currently filtering the top 15 topics out of 470 total.

The table correctly shows the average of each field, but I can't seem to recreate as a measure outside of the table, that does the same thing. I can create averages for grand totals, but unable to recreate the averages in the table.

So far this is how far I got for one of the fields

AVERAGEX(

    TOPN(15, 'Topics'),

    SUM('Topics'[RowCount])

    )
which I hoped would give me the Sum based off of the filter (sum of top 15), and then divide 15/Sum.
but it keeps giving me the grand total of the entire field, ignores the TopN 15 filter. 

How can I create a measure that shows each of the bolded averages below, based off the # of records being filtered rather than the total table?

Any help is appreciated, of course!

Steve

[v-zhangti · Accepted solution]

Hi, @Anonymous 

You can try the following methods.
Measure:

Rank = RANKX(ALL('Table'),CALCULATE(SUM('Table'[RowCount])),,DESC)

Average rowcount = CALCULATE(SUM('Table'[RowCount]),FILTER(ALL('Table'),[Rank]<=15))/15

Average Look&Feel = CALCULATE(SUM('Table'[Look & Feel]),FILTER(ALL('Table'),[Rank]<=15))/15

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Thanks very much for this, it was a big help and it helped me resolve my challenge.  Thanks!

[Ashish_Mathur]

Hi,

Does this get the right answer?

Total = sum('Topics'[RowCount])

Average of Top 15 = AVERAGEX(TOPN(15, 'Topics'),[Total])

If not, then share the download link of the PBI file.

[parry2k]

@Anonymous what do you define top 15? Top based on what? 

[foodd]

Please create a pbix file that contains some sample data but still reflects your data model (tables, relationships, calculated columns, and measures), upload the pbix to Onedrive or Dropbox, and share the link. Please use Excel to create the sample data instead of the manual input method share the xlsx as well.

Describe the expected results based on the sample you provide.