Stop recalculation of matrix table when filtering the table to show topN values

[ArT101]

I'm trying to get my matrix table to show these values:

when I filter the table to show the the top 1st name it filters the value to:

[ArT101 · Accepted solution]

https://goodly.co.in/top-n-and-others-power-bi/

I used the following blog to help me with my problem. Had to tweak the code a lil to match the measures I calculated but it works as expected.

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[ArT101]

Hopefully this screenshot will suffice for you.

[Ashish_Mathur]

Hi,

Do away with the Over hits measure.  Revise the % measure to

=divide([Measure test],[Hit])

Hope this helps.

[ArT101]

No it still recalculate the percentage to 100%

I want the percentage to stay the way it was before the table was filtered

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-yangliu-msft]

Hi  @ArT101 ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

Sum =
 var _table=SUMMARIZE('Table','Table'[Name],
 "Sum",SUMX(FILTER(ALL('Table'),'Table'[Name]=EARLIER('Table'[Name])),[Amount]))
return
ADDCOLUMNS(
    _table,"Rank",RANKX(_table,[Sum],,DESC))

2. Create measure.

Measure test =
SUMX(FILTER(ALL('Table'),'Table'[Name]=MAX('Table'[Name])),[Amount])

Overalll Hits =
SUMX(ALL('Table'),[Amount])

% =
DIVIDE(
    [Measure test],[Overalll Hits])

rank =
MAXX(FILTER(ALL('Sum'),'Sum'[Name]=MAX('Table'[Name])),[Rank])

Flag =
IF(
    [rank]=1,1,0)

Before filtering:

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

After filtering:

Best Regards,

Liu Yang

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

[ArT101]

Thank you greatly for the help. My only issue is the summing of the amounts. I am aware that my first message was vague with the explanation because I thought the process would be much simpler then the method you provided.

So for more information. My table is basically showing the number of times a match has been made against specific names provided by a user, which is what is showing in the name column. Even though multiple names can be duplicated, their Id's make them unique. So while I do want to be able to know what the values of the name A adds up to, I still want to keep them as individual row values. Like the image shows:

This is my code used:

Measure test = SUMX(VALUES(Table name'[Column name]), CALCULATE(DISTINCTCOUNT('Table name'[Column name]), 'Table name'[Column name] <> BLANK()))

Then for the overall hits, I created a measure counting the overall matches made within the table.I wanted to use the total of this measure to divide for my % so I used this code:

Overall hits = CALCULATE([Measure], ALLSELECTED())

Then that's how I created my %: Measure Test/Overall hits.

The overall hits changes depending on the date filter. So the value isn't constant.

Hopefully this makes sense.