powerpivotdaxpowerview

Top 5 and Bottom 5 records in Power Pivot

I am trying to show top 5 records and bottom 5 records in two separate tables in Power View.

I have created a calculated measure to show the records.

But it doesnt giving me the required result.

My Data Set is:- 

ID  Name    Rating  OverallRating   Ranking
1   A       124     
2   B       156     
3   C       123     
4   D       124     
5   E       125     
6   F       143     
7   G       124     
8   H       123     
9   I       156     
10  A       178     
11  B       187     
12  C       123     
13  H       123     
14  I       134     
15  D       138     
16  E        50     
17  F        90     
18  G        70

I also have the following measure

[Count]=SUMX(SUMMARIZE('Details',[Name],"Value",COUNTA([ID])),[Value])

Overall Rating is calculated based on:- Sum of rating for each Name divided by Count of [ID] for that name.

OverallRating=SUMX(SUMMARIZE('Details',[Name],"value2",([Rating])/[Count]),[value2])

then I am calculating rank on this overall rating.

Ranking=RANKX(ALL(Details),[overallRating],,0,Dense)

Now I want to Top 5 and Bottom 5 names with OverallRatings in Power View Table

for top 5 records I am using the below query:- 

Top 5 name:=CALCULATE(SUMX(SUMMARIZE('Details',[Name],"value2",(SUM([overallRating]))),[value2]),FILTER('Details',RANKX(ALL('Details'),[Ranking])<=5))

can someone help me in this where I am doing wrong or should i need to try something else.

Your help will be appreciated.

Solution

  • If you are using pivot report, then you can use filter option to achieve this.