Filtering a visual in Power BI by TOPN or BOTTOMN is very easy. You apply a visual level filter and specifiy how many TOP/BOTTOM values you wan to see in your visual. Things get complicated however when there is a hierarchy present in the visual.The TOPN filter will work for the level you specified in the visual level filter section but not for other levels. Suppose you have a matrix with Country and City as the rows and [Total Sales] as the measure and you specify a TOP10 filter for Country. So at the Country level, you will get the right result and the TOP10 values will be shown. But when you drilldown to the City level, the TOP10 values will be incorrect. It will show you all the cities in the countries which make up the TOP10 which is not we want. We want to see the TOP10 cities irrespective of whether the country is in the TOP10 values.

Lets see with an example.The matrix below has Country and City as Rows and [SalesAmount] as the measure. We apply a TOP10 filter on the visual for Country and you can see the TOP10 countries in the visual for SalesAmount.

Two things .happen when we go to the next leve in the hierarchy.

• The matrix now shows all the cities which are part of the TOP10 countries in a descending order of SalesAmount. If there is more than one city in a Country you will get more than 10 cities in the matrix. This is because the TOP10 filter is on Country and not on City.
• The TOP10 cities will be incorrect.

The matrix above shows the result of drilling down to the City level. The cities within the Red box form the TOP10.Now lets take another Matrix where we only have Cities in Rows and [SalesAmount] as a measure and lets apply a TOP10 filter on the matrix, Lets place this matrix to the right of the above matrix.

We see that the TOP9 cities on the right matrix are the same as the left one. But the 10th city Toronto is missing from the left. This is because Toronto’s parent is Canada and it wasnt in the TOP10 countries. And because we cannot have two TOPN filters on the same visual we will get incorrect results like this. So if want to be able to view the actual TOP10 cities when we drilldown we need to use DAX to compute it. This blog will show you how to do it.

So the problem statement is as follows:

Create a matrix with two levels of hierarchy – Country and City. At the Country level show TOP10 countries by SalesAmount and when you go down to the City level show TOP10 cities by SalesAmount regardless of whether the parent company is in the TOP10 countries list. Also show the % of SalesAmount of the selected total (in this case for TOP10 countries and cities) at three levels

• Country level
• When you expand one country show the % of total for all Cities under it
• City level

The final DAX is quite complex, so lets break it down into chunks and solve smaller sections and merge them together in the end. There are five sections to the problem , and all of them requires a unique logic.

1. At the Country level and where the selected Country is part of the TOP10 countries list – M1
2. At the Country level and where the selected Country is NOT part of the TOP10 countries list – M2
3. When you expand one Country and the Country is part of the TOP10 countries list – M3
4. At the City level and where the selected City is part of the TOP10 cities list – M4
5. At the City level and where the selected City is NOT part of the TOP10 cities list – M5

Before we start writing the logic for each section we need to define some variable swhich we will be using throughout the code.

VAR SalesAmt = [SalesAmount]
VAR top10CountryName =
    TOPN (
        10,
        ALL ( ‘Region'[Country] ),
        CALCULATE ( [SalesAmount], REMOVEFILTERS ( Region[City] ) )
    )
VAR top10CityName =
    TOPN (10,  ALLSELECTED ( ‘Region'[City] ), [SalesAmount] )
VAR IsInScopeCountryName =
    ISINSCOPE ( ‘Region'[Country] )
VAR IsFilteredCity =
    ISFILTERED ( ‘Region'[City] )
VAR SelValueCountryName =
    SELECTEDVALUE ( ‘Region'[Country] )
VAR SelValueCityName =
    SELECTEDVALUE ( ‘Region'[City] )
VAR DistCountInstName =
    CALCULATE (
        DISTINCTCOUNT ( ‘Region'[City] ),
        ALLEXCEPT ( ‘Region’, ‘Region'[Country] ),
        FILTER ( ALLSELECTED ( ‘Region'[City] ), [SalesAmount] > 0 )
    )

Most of them are self explanatory but two require our attention. top10CountryName and DistCountInstName

top10CountryName has this code as the 3rd argument of the TOPN function.

CALCULATE ( [SalesAmount], REMOVEFILTERS ( Region[City] ) )

This is important because when the function over iterates over a City to get the TOP10 countries we need to remove the filters from the intial filter context on City else we will get incorrect results.

DistCountInstName gives the distinct count of cities when iterating over a Country

Lets start solving for each section. The code for M1 is as below. This gives the % of Sales Amount for that country if its in TOP10 countries list.

           IF (
                IsInScopeCountryName
                    && SelValueCountryName IN top10CountryName,
                DIVIDE (
                    SalesAmt,
                    CALCULATE ( [SalesAmount], ALLSELECTED ( Region[Country] ) )
                )

The logic for M2 is as follows. If a country is not in the TOP10 countries list return BLANK

        IF (
            IsInScopeCountryName
                && NOT ( SelValueCountryName ) IN top10CountryName,
            BLANK (),
            )

The logic for M3 is as follows. When a City is filtered and when a Country is in scope and when the selected city name is not blank then give the % sales amount for the cities for that expanded Country.
                IF (
                    IsInScopeCountryName && IsFilteredCity
                        && NOT ( ISBLANK ( SelValueCityName ) )
                            && SelValueCountryName IN top10CountryName,
                    DIVIDE (
                        SalesAmt,
                        CALCULATE ( [SalesAmount], ALLEXCEPT ( ‘Region’, ‘Region'[Country] ) ) — When you expand one Country
                    )

The logic for M4 is as follows. At the City level, if the selected city is in the TOP10 cities list give the % sales amount for the cities.

                    IF (
                        IsFilteredCity
                            && SelValueCityName IN top10CityName,
                        DIVIDE ( SalesAmt, CALCULATE ( [SalesAmount], top10CityName )
                       )

The logic for M5 is as follows. At the City level, if the selected city is NOT in the TOP10 cities list return BLANK
                       
                        IF (
                            IsFilteredCity
                                && NOT ( SelValueCityName ) IN top10CityName,
                            BLANK ()
                        )

Putting this all together and adding logic for Totals we get this code

Sales % of Total =
VAR SalesAmt = [SalesAmount]
VAR top10CountryName =
    TOPN (
        [TOPN Parameter Value],
        ALL ( ‘Region'[Country] ),
        CALCULATE ( [SalesAmount], REMOVEFILTERS ( Region[City] ) )
    )
VAR top10CityName =
    TOPN ( [TOPN Parameter Value], ALLSELECTED ( ‘Region'[City] ), [SalesAmount] )
VAR IsInScopeCountryName =
    ISINSCOPE ( ‘Region'[Country] )
VAR IsFilteredCity =
    ISFILTERED ( ‘Region'[City] )
VAR SelValueCountryName =
    SELECTEDVALUE ( ‘Region'[Country] )
VAR SelValueCityName =
    SELECTEDVALUE ( ‘Region'[City] )
VAR DistCountInstName =
    CALCULATE (
        DISTINCTCOUNT ( ‘Region'[City] ),
        ALLEXCEPT ( ‘Region’, ‘Region'[Country] ),
        FILTER ( ALLSELECTED ( ‘Region'[City] ), [SalesAmount] > 0 )
    )
RETURN
    IF (
        NOT ( HASONEVALUE ( ‘Region'[Country] ) ),
        1,   — For Total at Country level
        IF (
            IsInScopeCountryName
                && NOT ( SelValueCountryName ) IN top10CountryName,
            BLANK (),
            IF (
                IsInScopeCountryName
                    && SelValueCountryName IN top10CountryName,
                DIVIDE (
                    SalesAmt,
                    CALCULATE ( [SalesAmount], ALLSELECTED ( Region[Country] ) ) — At the Country level
                ),
                IF (
                    IsInScopeCountryName && IsFilteredCity
                        && NOT ( ISBLANK ( SelValueCityName ) )
                            && SelValueCountryName IN top10CountryName,
                    DIVIDE (
                        SalesAmt,
                        CALCULATE ( [SalesAmount], ALLEXCEPT ( ‘Region’, ‘Region'[Country] ) ) — When you expand one Country
                    ),
                    IF (
                        IsFilteredCity
                            && SelValueCityName IN top10CityName,
                        DIVIDE ( SalesAmt, CALCULATE ( [SalesAmount], top10CityName ) ),
                        — When you go to next level in the hierarchy for Top 10 City
                        IF (
                            IsFilteredCity
                                && NOT ( SelValueCityName ) IN top10CityName,
                            BLANK (),
                            — When you go to next level in the hierarchy for outside of Top 10 City
                            1 — For Total at City level
                        )
                    )
                )
            )
        )
    )

Lets apply this measure to the matrix and explore the results. We see that it is working as expected at the Country level and City level. But when you expand a Country the number at the Cities for that country is all 100%. We need to fix this.

We need to change the logic in M2, Because currently the logic for M3 is not being evaluated as M2 is taking precedence. The new code is as follows with some comments.When we are iterating over a Country and that country is in the TOP10 countries list we also need to ensure that the selected city name is blank or there is only one city under the Country.
IF (
    IsInScopeCountryName
        && (
            ISBLANK ( SelValueCityName ) — Selected City is blank (More than one City exist) OR
                || DistCountInstName = 1 — Only one City exits
        )
        && SelValueCountryName IN top10CountryName,
    DIVIDE (
        SalesAmt,
        CALCULATE ( [SalesAmount], ALLSELECTED ( Region[Country] ) ) — At the Country level
    )
)

Substituting this in the main code we get the final code

Sales % of Total =
VAR SalesAmt = [SalesAmount]
VAR top10CountryName =
    TOPN (
        [TOPN Parameter Value],
        ALL ( ‘Region'[Country] ),
        CALCULATE ( [SalesAmount], REMOVEFILTERS ( Region[City] ) )
    )
VAR top10CityName =
    TOPN ( [TOPN Parameter Value], ALLSELECTED ( ‘Region'[City] ), [SalesAmount] )
VAR IsInScopeCountryName =
    ISINSCOPE ( ‘Region'[Country] )
VAR IsFilteredCity =
    ISFILTERED ( ‘Region'[City] )
VAR SelValueCountryName =
    SELECTEDVALUE ( ‘Region'[Country] )
VAR SelValueCityName =
    SELECTEDVALUE ( ‘Region'[City] )
VAR DistCountInstName =
    CALCULATE (
        DISTINCTCOUNT ( ‘Region'[City] ),
        ALLEXCEPT ( ‘Region’, ‘Region'[Country] ),
        FILTER ( ALLSELECTED ( ‘Region'[City] ), [SalesAmount] > 0 )
    )
RETURN
    IF (
        NOT ( HASONEVALUE ( ‘Region'[Country] ) ),
        1,
        — For Total at Country level
        IF (
            IsInScopeCountryName
                && NOT ( SelValueCountryName ) IN top10CountryName,
            BLANK (),
            IF (
                IsInScopeCountryName –&& NOT(IsFilteredCity)
                    && (
                        ISBLANK ( SelValueCityName ) — Selected City is blank (More than one City exist) OR
                            || DistCountInstName = 1 — Only one City exits
                    )
                    && SelValueCountryName IN top10CountryName,
                DIVIDE (
                    SalesAmt,
                    CALCULATE ( [SalesAmount], ALLSELECTED ( Region[Country] ) ) — At the Country level
                ),
                IF (
                    IsInScopeCountryName && IsFilteredCity
                        && NOT ( ISBLANK ( SelValueCityName ) )
                            && SelValueCountryName IN top10CountryName,
                    DIVIDE (
                        SalesAmt,
                        CALCULATE ( [SalesAmount], ALLEXCEPT ( ‘Region’, ‘Region'[Country] ) ) — When you expand one Country
                    ),
                    IF (
                        IsFilteredCity
                            && SelValueCityName IN top10CityName,
                        DIVIDE ( SalesAmt, CALCULATE ( [SalesAmount], top10CityName ) ),
                        — When you go to next level in the hierarchy for Top 10 City
                        IF (
                            IsFilteredCity
                                && NOT ( SelValueCityName ) IN top10CityName,
                            BLANK (),
                            — When you go to next level in the hierarchy for outside of Top 10 City
                            1 — For Total at City level
                        )
                    )
                )
            )
        )
    )

Now this code works at all levels