How can i apply two different Top N filters on a visual at the same time?

[tauhid9231]

Hello Power BI Community,

I hope you're all doing well. I'm currently working on a project where I need to display the top 5 products by GM% Variance for the top 5 countries (markets) based on selected metrics like Net Sales, Gross Margin %, or Net Profit %. I've managed to create the necessary base measures and slicers for selecting the metric, but I'm facing some challenges in implementing a second top N filter for the countries. I attempted to create a measure called "market_rank" using the following DAX measure:

market_rank =
RANKX(
    ALLSELECTED(dim_market[market]),
    [top5country_byfilter],          -- selects NS/ GM% / or NP % based on selection by which to filter top 5 countries(markets)
    , DESC,
    Dense
)

However, when I apply this measure as a filter (less than or equal to 5) on my product matrix visual, the results are incorrect. It seems to work fine only when I manually select all the top 5 countries. I believe I'm close to achieving the desired outcome but need some assistance in getting it right.

NOTE:
I need to use year and quarter slicers on the same report page as well. I am also providing a snapshot of my data model and some screenshots for clarity.

part of my data model which is relevant here 

Correct values when I manually select all those top 5 markets (Ctrl+Click)

Could someone please help me simplify and correct this measure, or provide an alternative approach to achieving the desired outcome?

Thank you in advance for your assistance!

[talespin · Accepted solution]

Hi @tauhid9231 

The problem lies in your model, which is why I stopped.

One of the table used by measue [GM % Variance] is "fact_NsGmTarget" - This table can be filtered by Market but not by Product.

Please fix your Datamodel and try the above approach which I shared for Contoso DM.

[tauhid9231 · Accepted solution]

Finally got it solved! After exploring several approaches, I achieved the desired output with a much simpler solution and a calm, fresh mindset. The key is persistence.
The approach that worked involved initially filtering the matrix for the top 5 markets and then utilizing a Product Rank measure to filter for the top 5 products. Sharing the formulas used...

Product Rank =

RANKX(

    ALLSELECTED(dim_product[product]),

    [GM % △YoY]

)

Earlier, I forgot to mention that the targets were not available at the product level, so I created the simplified version of GM % Variance i.e., GM % △YoY. 

Heartfelt thanks to @talespin for the guidance and support throughout the process! 🙏

[talespin]

hi @tauhid9231 

Thank you for understanding. In case you face any problem, please share pbix with mockup data, few records that cover your use case should suffice.

[tauhid9231]

Hi @talespin ,

I've been trying different approaches but can't seem to get it right. Could you please take a look at the fresh PBIX file I've shared via the Google Drive link? Your expertise would really help me improve my skills. Thanks so much for always responding to my questions, I truly appreciate it.

[talespin · Accepted solution]

Hi @tauhid9231 

The problem lies in your model, which is why I stopped.

One of the table used by measue [GM % Variance] is "fact_NsGmTarget" - This table can be filtered by Market but not by Product.

Please fix your Datamodel and try the above approach which I shared for Contoso DM.

[tauhid9231]

Thanks for pointing it out @talespin , I will fix it. 

[talespin]

hi @tauhid9231 

What I have shared is just an example to show you one method on how you can achieve your objective. It is not a find/replace thing.

Replace OrdersRows with your FACT table.

Country and Product with your Dimensions.

In place of SalesAmount and ProductQuantuty you will need to provide your measures, instead of calling a measure I would advise first doing calculation directly in single measure.

Above all, thoroughly test your result.

[tauhid9231]

@talespin
I completely understand and agree with you that this is not a copy-paste solution. I was simply trying to follow your approach in my data model. I will give it my best shot and then come back to you again to extend my gratitude.

[tauhid9231]

Hello @talespin,

Thank you so much for taking the time to provide such a detailed response to my question. Your solution seems to be exactly what I need for my data model, and I'm excited to implement it.

I have a quick question regarding the formula you provided. In place of 'OrderRows' table, what should I use for my data model? Could you please provide some guidance on this?

Once I've successfully implemented your solution, I'll be sure to mark it as the accepted solution. Thank you once again for your assistance!

[talespin]

hi @tauhid9231 

Can you share measure for GM% Variance.

How I would do it is 

First rank countries and retrieve all data for Top 5 countires and then rank within each country.

[tauhid9231]

Thanks for the reply @talespin , sure I will share it. But due to the requirements of my project, it has dependencies on several other measures as well. I am also providing the definition of all of them alongside from the DAX query view.

DEFINE

    MEASURE '*metrics*'[NS $] = SUM(

    fact_actuals_estimates[net_sales_amt]

)

    MEASURE '*metrics*'[Manufacturing Costs $] = SUM(

    fact_actuals_estimates[total_manufacturing_cost]

)*-1

    MEASURE '*metrics*'[Freight Costs $] = SUM(

    fact_actuals_estimates[total_freight_cost]

)*-1

    MEASURE '*metrics*'[Other Costs $] = SUM(

    fact_actuals_estimates[other_costs]

)*-1

    MEASURE '*metrics*'[Total COGS $] = -[Manufacturing Costs $] - [Freight Costs $] - [Other Costs $]

    MEASURE '*metrics*'[GM $] = [NS $] - [Total COGS $]

    MEASURE '*metrics*'[GM %] = DIVIDE(

    [GM $],

    [NS $],

    0

)

    MEASURE '*metrics*'[GM % Target] = VAR _currency =

    SUM(fact_NsGmTarget[gm_target])

RETURN

    DIVIDE(

        _currency,

        SUM(fact_NsGmTarget[ns_target]),

        0

    )

    MEASURE '*metrics*'[GM % LY] = CALCULATE(

    [GM %],

    SAMEPERIODLASTYEAR(dim_date[date])

)

    MEASURE '*metrics*'[GM % BM] = IF(

    ISFILTERED(Toggle[Toggle]),

    [GM % Target],

    [GM % LY]

)

    MEASURE '*metrics*'[GM % Variance] = [GM %] - [GM % BM]

// +ve GM % Variance means exceeded Benchmark(BM=Last Year Value or Target), -ve means lagging behind BM.

 
Now as you said, "first rank countries and retrieve all data for Top 5 countires and then rank within each country". I will try doing that, but rather than showing top 5 product for EACH of the top 5 countries, I want to show only top 5 products from those 5 countries combined.

Do let me know, if you need more information. 

[tauhid9231]

Well I was wondering, can we use CROSSFILTER function (between dim_product and fact_actuals) to create a measure so that the filter context coming from the dim_market table can propagate to dim_product table through all connected tables and use that measure to filter our product Matrix for such top 5 markets? (Just a thought, don't know whether it will work, also not sure how can I accomplish the same). Would appreciate any idea on that as well!

[talespin]

hi @tauhid9231 

From your Datamodel Perspective
Product Filters Fact
Country Filters Fact

I am using Contoso to simulate your scenario(Country by Sales and then Product by Product sold in these top 5 countries).
-First Group by All Product, Country
-Calculate Sales at country level
-Select Top 5 Countries along with products sold in these countries.
-Calculate Products sold by Country and Product

-Group by Products and aggregate Product sold in top5 countries.

-Return Rank of these products

Question - What if same product(one of the top products) is sold in different countries?

Below measure counts product quantity sold only in top 5 countries, it excludes any quantity sold in countries other than top 5.

-------------------------------------------------------------------------

Sales Amount = SUM(OrderRows[Sales Amount])

-------------------------------------------------------------------------

Top 5 Products by Top 5 Countries =

VAR _SummTbl =

ADDCOLUMNS(

            SUMMARIZE(ALLSELECTED(OrderRows), Customer[CountryFull], 'Product'[Product Name]),

            "@CountrySales",

            VAR _Country = [CountryFull]

            RETURN CALCULATE([Sales Amount], REMOVEFILTERS('Product'[Product Name]))            

)

VAR _RankByCountry =

ADDCOLUMNS(

            _SummTbl,

            "@RankCountryBySales",

            RANK(DENSE, _SummTbl, ORDERBY([@CountrySales], DESC))

)

VAR _FilterCtry = FILTER(_RankByCountry, [@RankCountryBySales] <= 5)

VAR _SummTbl2 =

ADDCOLUMNS(

            _FilterCtry,

            "@ProductsSold",            

            VAR _ProductName = [Product Name]

            VAR _Country = [CountryFull]

            RETURN CALCULATE( SUM(OrderRows[Quantity]), REMOVEFILTERS(), 'Product'[Product Name] = _ProductName, Customer[CountryFull] = _Country)

        )

VAR _SummTbl3 =          

ADDCOLUMNS(

            SUMMARIZE(_FilterCtry, 'Product'[Product Name]),

            "@TotalProductsSold",

            VAR _ProdName = [Product Name]

            RETURN SUMX( FILTER(_SummTbl2, 'Product'[Product Name] = _ProdName), [@ProductsSold])

)

RETURN RANK(DENSE, _SummTbl3, ORDERBY([@TotalProductsSold], DESC))