Multiple TopN+Other

[Anonymous]

Hi,

I have some (lots) of data regarding various projects that I want to show in a table with the top10+others based on the projects revenue. This part I've done using this guide . However, I want to add more columns to the table visual, but this messes upp the table so that I have more than those 11 rows. with some fields being blank. I tried making similar measures such like in the guide, but with the "primary" measures Rank in the other ones aswell, but this makes another error, now all the fields in this measure have the exact same number. My guess is because this field is coming from another table? 

How my data is stored:

Table 1: Project name, Project Revenue (this is the one I want top10 to be based on), Region, Type, Profitability 

Table 2: Project name (used as relationsip with table 1, one to many), ContractFee, RevenueStart

(plus some additional measures from both tables, but these are the main ones)

So TLDR: How do I create the TopN measure so that it works between tables?

I've tried using this measure within Table1:

ContractFeeMeasure = SUM(Table2[ContractFee])

The measure above was used in the TopContractFee measure according to the guide, but with the TopProject measure's rank thing as rank instead of a new one for ContractFee, since I want it based on the projects revenue.

TopContractFee = IF([ProjectRank] <= [TopX], [ContractFeeMeasure], 
IF(SELECTEDVALUE(ReferenceTable[Project name]) = "Other", SUMX(FILTER(ALL('ReferenceTable'[Project name]), [ProjectRank] > [TopX]), [ContractFeeMeasure])))

[v-kelly-msft]

Hi @Anonymous ，

Can you provide some sample data with expected output?

Best Regards,
Kelly

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

[Anonymous · Accepted solution]

Thanks for the reply:

This is the result that I get now:

Project name  TopProject       TopContractFee

Other                 565 026 730,89                           4 927 182 316 623,78   

A                          99 951 382,82                             4 797 645 877,92   

B                          85 659 326,07                             4 797 645 877,92   

C                          82 688 864,48                             4 797 645 877,92   

D                          76 639 108,11                             4 797 645 877,92   

E                           37 111 767,75                             4 797 645 877,92   

F                           32 147 334,19                             4 797 645 877,92   

G                          26 049 779,71                             4 797 645 877,92    

H                          24 253 747,97                             4 797 645 877,92   

I                            24 204 830,78                             4 797 645 877,92   

J                           20 808 383,51                             4 797 645 877,92   

And as you can see the ContractFee column shows the same values for all rows. 

This is how I want it to look:

Project name  TopProject       TopContractFeeStart

Other                 570 486 691,67                           3 102 723 681,85   

A                          99 951 382,82                             511 315 997,00   

B                          85 659 326,07                             175 262 728,50   

C                          82 688 864,48                             554 118 996,67   

D                          76 639 108,11                             247 040 310,49   

E                           37 111 767,75                             60 523 258,30   

F                           32 147 334,19                             39 662 351,00   

G                          26 049 780,58                             45 075 332,00   

H                          24 253 747,97                             50 685 531,00   

I                            24 204 830,78                             70 020 001,00   

J                           20 808 383,51                             47 087 448,00   

I managed to get it working, I changed the relationship between the new table that is created according to the guide. I changed from Table1 to Table2 and it seems to have solved everything. Which is weird, because the other day I got an error when I tried to activate that relationship... Which is why I made this post.