Filtering Top 3 Cities & top 3 sales managers within the cities

[Anonymous]

I'm trying to create a matrix with the top 3 cities & top 3 sales managers within the cities. So far i managed the ranking till the cities. Request help to include the ranking of the sales managers too.

The below dax currently ranks all zones and the top 3 cities within each zone.

DAX so far-

Rank_Zone_City = IF (
ISINSCOPE( 'Location'[City] ),
RANKX (
CALCULATETABLE (
VALUES ( 'Location'[City] ),
ALLSELECTED ( 'Location'[City] )
),
[Sales Last Month]
),
IF (
ISINSCOPE( 'Location'[Zone] ),
RANKX (
ALLSELECTED ( 'Location'[Zone] ),
[Sales Last Month]
)
)
)

Appreciate the help!

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a measure as below.

Visual Control = 
var tab = 
CALCULATETABLE(
    DISTINCT('Table'[City]),
    TOPN(
        3,
        SUMMARIZE(
             ALL('Table'),
             'Table'[City],
             "Sum",
             SUM('Table'[Sales])
        ),
        [Sum]
    )
)
var newtab = 
CALCULATETABLE(
    DISTINCT('Table'[Zone]),
    TOPN(
        3,
        SUMMARIZE(
            FILTER(
                ALL('Table'),
                'Table'[City]=SELECTEDVALUE('Table'[City])
            ), 
            [Zone],
            "Sum",
            SUM('Table'[Sales])
        ),
        [Sum]
    )
)
return
    IF(
        ISINSCOPE('Table'[Zone])&&SELECTEDVALUE('Table'[City]) in tab,
        IF(
            SELECTEDVALUE('Table'[Zone]) in newtab,
            1,0
        ),
        IF(
            ISINSCOPE('Table'[City]),
            IF(
                SELECTEDVALUE('Table'[City]) in tab,
                1,0
            )
        )
    )

Finally you need to put the measure in the visual level filter to filter the result.

Without filter:

Filtered result:

Best Regards

Allan

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

[Anonymous]

@v-alq-msft i'm trying to implement the below logic -
Zones - North, South, etc --> should show up total sales for the zone (not just top 3 city total)
Cities - Delhi, Punjab, Haryana…etc --> should show up total sales for the city (not just top 3 sales manager sales total)
Sales Managers - A,B, C, D,…. T --> should show up total sales for the indivudal sales managers within that city & zone

And my matrix should look like - 

	Rank	Unfiltered sales
North	1	100
Delhi	1	30
A	1	5
B	2	3
C	3	2
Punjab	2	10
D	1	4
E	2	2
F	3	1
Haryana	3	9
G	1	3
H	2	2
I	3	1
South	2	90
Kerela	1	30
L	1	10
M	2	6
N	3	4
Tamil Nadu	2	20
0	1	10
P	2	3
Q	3	2
Pondicherry	3	15
R	1	7
S	2	2
T	3	1

[amitchandak]

@Anonymous , refer

https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale

https://blog.gbrueckl.at/2019/05/power-bi-dynamic-topn-others-with-drill-down/
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

@amitchandak  https://www.sqlbi.com/articles/filtering-the-top-3-products-for-each-category-in-power-bi/ is limited to only 2 hierarchies, all the other rank articles shared either creates different rank columns for the sub category and category ( i need it on the same column) or shows for only two hierarchies