0

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.

JAL
  • 41,701
  • 23
  • 172
  • 300
BIDeveloper
  • 767
  • 3
  • 14
  • 28
  • You need to include all the columns in your sample data. I can't recreate your current calculations: i.e. `OverallRating` has column the [Count] column not included in your data. – guitarthrower Feb 26 '15 at 18:05
  • hi @guitarthrower Count is nothing but the count of ID group by Name. formula for that I am using is [Count]=SUMX(SUMMARIZE('Details',[Name],"Value",COUNTA([ID])),[Value]) – BIDeveloper Feb 27 '15 at 05:28

2 Answers2

0

I haven't used PowerView yet, but getting to the correct ranking is the key to your question.

First thing to note, I haven't added any calculated columns. Instead, I've used the following 4 measures, that build off each other to get to the Ranking.

CountByName (To replace your count measure)

CountByName:=CALCULATE(COUNTA(Details[Name]))

RatingByName (sums the ratings for each name)

RatingByName:=CALCULATE(SUM(Details[Rating]))

OverallRating (rating/count for each name)

OverallRating:=[RatingByName]/[CountByName]

Ranking (Ranks based on the OverallRating)

Ranking:=RANKX(ALL(Details[Name]),[OverallRating])

Since these are all measures, you could create a PivotTable to test they are working appropriately. (They do work based on my interpretation of your data). It ranks the Names in the following order

1  2  3  4  5  6  7  8  9
B  A  I  D  H  C  F  G  E

Based on my understanding of PowerView, you should be able to use the [Ranking] measure to return top 5 and bottom 5.

guitarthrower
  • 5,624
  • 3
  • 29
  • 37
  • Hi @guitarthrower, till ranking I had already done, and now I want the top 5 records from this, I am writing a query using TOPN function, since it is a calculated measure I need to sum up the result after doing all that I am not getting the desired results. Please see the query which I am using. Top 5 PM:=SUMX(TOPN(5,SUMMARIZE('Details',[Name],"Top PM", [Top 5 PM]),(SUM([OverallRating])/COUNTROWS(Details))),SUMX('Details',[Ranking])) – BIDeveloper Mar 02 '15 at 05:10
  • Again, not familiar with powerview, but my research says that all you need to do is filter for top 5 and bottom 5. See this link http://www.powerpivotblog.nl/filtering-results-to-only-show-top-x-results-in-power-view-chart/ – guitarthrower Mar 02 '15 at 16:20
  • that is something what I am using right now but in that I am having a problem if two names have the same rank then still i need to show only 5 instead of 6 or more than that..but this solution doesnt work for me – BIDeveloper Mar 03 '15 at 04:34
  • That would be helpful information to put in your question. What is your criteria for excluding the tied item? It seems misleading to exclude an item that has the same rank as another. – guitarthrower Mar 03 '15 at 16:07
0

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