Help with RANK (with COUNTROWS modifier)

[austin15g]

Hi all,

I have a Power BI visual for audit executive that is meant to count all of the high-level categories that are mentioned in his conversations with other executives in the business. Taking it a step further, I am looking to rank these categories so most discussed as ranked highest. The counting categories part is simple enough and taken care of. However, when I try to rank them, the ranking is not taking into account external filter context that I have on my report (i.e., filtering based  on which executive the conversation was with). In other words, the ranking is always looking at the source table as a whole, not a filtered-down source table.

Below are some screenshots. The first is of the matrix visual filtered on a specific executive. You can see that they only mentioned 3 processes and only once each. However, the ranking is ranking them differently because the categories in the unfiltered base table have different values. The second screenshot is the matrix visual unfiltered where you can see the Integration and Real Estate have lower counts, and what I believe the ranking in screenshot 1 is taking into account. The next two screenshots are of my counting measure and my ranking measure. The last screenshot is a snip of the source data (50 rows in total). Any help would be much appreciated thank you!

[austin15g · Accepted solution]

For anyone in the future looking at this: I was able to finally get it by using the following RANK measure and setting up my table in the following way. In the table, I just added a column that contained a 1 in every row that was used in the SUM portion of my RANK measure.  I opted to just use a calculated column in the table as opposed to going with a measure that counted the rows since I couldn't figure out how to make that work. As you'll see, I went through a few iterations of the calculated column: The first producing the sum of all Secondary Processes Mentioned in the table (will produce the same value for every line that has the same secondary process). The second being the sum of all Secondary Processes Mentioned for every Executive (Name). The last just being a 1 in every single row. I'm sure there's probably a more proper way of doing this, but this is how I achieved my desired result.

[amitchandak]

@austin15g , The Ranking is based on the Number of Mentions, and You are using #Discussion in the visual, are they the same, if not, please add the Number of Mentions in the visual and check the ranking.

Also you can use allselected('Fact Management mentions'[Seconday Process Mensioned])  in place summarize 

[austin15g]

Hi Amit, yes they are the same thing. I just changed the name in the visual for simplicity. I tried your suggestion of simply using ALLSELECTED instead of SUMMARIZE, but still giving everything a rank of 1 for some reason. I'll keep trying at it. Thank you for your suggestion though!