How to show top N rows in a specific hierarchy with a slicer?

[PowerBIBegin]

Hello,

My data is at the bottom of my post. My Power BI looks like the following screenshot. How can I add a slicer so that I can dynamically show top N rows for each city based on Count of Machine ID. For example, if N is 2, only Recife and Rio de Janeiro will show up under Brazil. Only Tokyo and Kyota will show up under Japan. In Argentina, since it only has Bluenos Aires, so only Bluenos Aires should show up.

Machine ID	City	Country	Continent	Year
213746	Kolwezi	DR Congo	Africa	2000
275601	Lubumbashi	DR Congo	Africa	1900
509142	Nagoya	Japan	Asia	2000
610214	Tokyo	Japan	Asia	1950
461479	Recife	Brazil	South America	2000
976554	São Paulo	Brazil	South America	2000
606025	São Paulo	Brazil	South America	1900
528691	Buenos Aires	Argentina	South America	2000
434051	Rio de Janeiro	Brazil	South America	1950
489893	Buenos Aires	Argentina	South America	2000
397222	Buenos Aires	Argentina	South America	1850
245315	Tokyo	Japan	Asia	2000
945377	São Paulo	Brazil	South America	1850

[MarkLaf]

I think this should add up correctly. Using numeric range parameter to set N. Selected N is referenced with the [Top N Value] measure, which PBI creates for you by default when setting up parameter.

Top N Cities Machine Count = 
VAR _aggRows =
    CALCULATETABLE (
        SUMMARIZECOLUMNS (
            Data[Country],
            Data[City],
            "MachineCount", COUNTROWS ( Data )
        ),
        ALLSELECTED ( Data )
    )
VAR _topNCities =
    WINDOW (
        0, ABS,
        [Top N Value], ABS,
        _aggRows,
        ORDERBY ( [MachineCount], DESC ),
        PARTITIONBY ( Data[Country] )
    )
RETURN
    CALCULATE (
        COUNTROWS ( Data ),
        KEEPFILTERS ( _topNCities )
    )

Note: assuming that the Machine ID is your primary key in the table, so using COUNTROWS rather than other count functions.

[v-achippa]

Hi @PowerBIBegin,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @MasonMA and @Ashish_Mathur for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

Thanks and regards,

Anjan Kumar Chippa

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[PowerBIBegin]

Hello Ashish,

Thanks for uploading your pbix file. I have one question. In the screenshot below, when I select Parameter = 2, The Measure of South America is 6 but actually it should be 8 (5 from Brazil and 3 from Argentina). Also, why the Total at the bottom is 6? Should it not be (Measure of South America + Measure of Asia + Measure of Africa) = 6 + 3 + 2 = 11?

[Ashish_Mathur]

You are welcome.  Share the download link of the PBI file.

[PowerBIBegin]

Hi Ashish, I can't find your link to the PBI file in your reply.

[Ashish_Mathur]

I have requested you for the download link.  Share that file (the picture of which you showed in your reply posted in Wednesday)

[PowerBIBegin]

Hello Ashish, please see this pbix file. When I set Parameter to 2. The number of South America is 6 but it should be 8 (5 from Brazil and 3 from Argentina). Also, the Total at the bottom is 6 but it should be (Measure of South America + Measure of Asia + Measure of Africa) = 6 + 3 + 2 = 11.

[Ashish_Mathur · Accepted solution]

Hi,

Use this measure

Measure 2 = SUMX(VALUES(Data[Country]),[Measure])

Hope this helps.

[MasonMA]

Hello @PowerBIBegin 

One of the solutions would be 'build a Numeric Parameter' and use this Parameter as Top N in your slicer. 

Under Modelling, you can create a 'Top N' parameter as below

Create one explicit measure to Count the Machines. 

Machine Count = COUNT('Table'[Machine ID])

 Use below Measure as Value in your Matrix to dynamically rank the Cities. 

Top N Cities = 
VAR SelectedN = SELECTEDVALUE('Top N'[Top N], 1) 
VAR CurrentCountry = SELECTEDVALUE('Table'[Country])
VAR CurrentCity = SELECTEDVALUE('Table'[City])
VAR CityRank = 
    RANKX(
        FILTER(
            ALLSELECTED('Table'[City], 'Table'[Country]),  
            'Table'[Country] = CurrentCountry
        ),
        CALCULATE([Machine Count]),  
        ,
        DESC,
        DENSE
    )

RETURN
    IF(
        CityRank <= SelectedN,
        [Machine Count],  
        BLANK()              
    )

With your sample data, i have below result (you can switch off the 'Row Subtotals' in the formatting pane)

Hope this helps:) 

[PowerBIBegin]

Hello @MasonMA 

Thanks for your reply. With your solution, I'm able to show top N cities. However, the number in the right column is not correct. In the following screenshot, I select 2 and it shows top 2 cities in the matrix, which is correct but somehow Japan is 101. I think it should be the sum of Kobe and Kyoto, which are 30 and 26, respectively. So Japan should be 56. Asian should be 56, too. This is the link to my pbix and data files.