Top 3 per category in a pivot table

[Anonymous]

Hello!

I am trying to group the top 3 stores for a metric by their district in the same pivot table. Example below...

I would like to show the top 3 in each district. Any help is appreciated!!!

[CNENFRNL · Accepted solution]

TOP_N = 
VAR __topn =
    TOPN(
        MAX( N[Value] ),
        CALCULATETABLE(
            SUMMARIZE( Data, Data[District], Data[Store] ),
            ALLSELECTED( Data[Store] )
        ),
        [Total]
    )
RETURN
    CALCULATE( [Total], KEEPFILTERS( __topn ) )

[Jihwan_Kim]

Hi,

In case you want to create a table, please check the below picture and the attached pbix file.

I tried to create a sample pbix file like below.

Top three per district table =
VAR _topthreestorelist =
    SUMMARIZE (
        GENERATE (
            VALUES ( Data[District] ),
            SUMMARIZE (
                TOPN (
                    3,
                    FILTER ( Data, Data[District] = EARLIER ( Data[District] ) ),
                    Data[Sales], DESC
                ),
                Data[Store]
            )
        ),
        Data[Store]
    )
RETURN
    CALCULATETABLE ( Data, Data[Store] IN _topthreestorelist )

[daXtreme]

Create a helper measure that will return 1 for any top 3 stores in any of the districts and then filter the visual by this value on this very measure. This will then hide the other stores and you'll only see what you want.