topic Re: Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other in DAX Commands and Tips

Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other

TFRec — Sat, 22 Jun 2024 19:04:44 GMT

I need to provide a matrix wherein I show the top 3 product codes, those codes' top 3 countries of origin, and those origin countries' top 3 destination countries.

I tried to use the below formula

Test Top 3 with multiple levels keep filter = VAR TOPNVALUE = 3 VAR TOPNCODE = TOPN(TOPNVALUE, ALLSELECTED(Table[Product_code]),[Sum Euros All Time]) VAR TOPNORIGINCOUNTRY = CALCULATETABLE( TOPN(TOPNVALUE, ALLSELECTED(Table[Origin_Country]),[Sum Euros All Time]), KEEPFILTERS(TOPNCODE) ) VAR TOPNDESTINATIONCOUNTRY = CALCULATETABLE( TOPN(TOPNVALUE, ALLSELECTED(Table[Destination_Country]),[Sum Euros All Time]), KEEPFILTERS(TOPNCODE), KEEPFILTERS(TOPNORIGINCOUNTRY) ) Return CALCULATE( [SumEuros], KEEPFILTERS(TOPNCODE), KEEPFILTERS(TOPNORIGINCOUNTRY), KEEPFILTERS(TOPNDESTINATIONCOUNTRY) )

It seems to work for most part, except that for some origin countries of some codes, for some reason it only shows one destination country.

I initially thought that maybe it was just that the origin in country only had one destination country, but no, it has multiple ones. As a matter of fact, for some reason, the total in the origin country (CR, 28.576 million) is the accurate total of the top 3 countries despite it only showing 1 top country.

The sum formulas referenced in the first DAX formula do not seem to be the issue from what I have tested, but just in case they are:

Sum Euros All Time = VAR time_period_min = MINX(ALLSELECTED(Table[Date]),Table[Date]) VAR time_period_max = MAXX(ALLSELECTED(Table[Date]),Table[Date]) Return CALCULATE( [SumEuros], ALL(Table[Date]), Table[Date]>=time_period_min, Table[Date]<=time_period_max )

SumEuros = SUM(Table[Sales])

Re: Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other

lbendlin — Wed, 19 Jun 2024 21:19:45 GMT

You need to take extra care to use separate calculations for each category.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other

TFRec — Thu, 20 Jun 2024 15:12:56 GMT

Hello lbendlin ,

Thank you for the help. I have uploaded a pbix using publicly available trade data to replicate the issue at https://we.tl/t-KFNe32hvK9? (hope it is all right to give a WeTransfer link, it was mentioned on the upload data link as one of the ok mediums).

You will see there that for code "27090090" under partner country "NL" it only shows "BE" and "DE", but it should also show "ES" as they are the third biggest declarant country with 191 Million EUR for the code and partner country in question.

Edit: I initially accidentally replied to topic first.

Re: Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other

lbendlin — Thu, 20 Jun 2024 22:30:28 GMT

on the top two levels do you want to show the total level value or only the value contributed by the underlying top 3?

Re: Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other

TFRec — Sat, 22 Jun 2024 05:16:12 GMT

Hello lbendlin ,

Thank you very much for having taken the time to look into this. Looking to have the value contributed by the underlying top 3, as it is working in your approach. Am however struggling to change the formula in such a way that it still does the top 3 for the other two upper levels as well.

Re: Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other

qqqqqwwwweeerrr — Sat, 22 Jun 2024 05:45:06 GMT

Hi TFRec

lbendlin already provided a very good approach, but still, if you seeking another way of doing that you can check this video this is not exact but with some modification this might help you to attain solution: https://youtu.be/cs2AwJljBrQ?si=yQR7T5WZZMfTaSDh

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Check for more intersing solution here: www.youtube.com/@Howtosolveprobem

Regards

Re: Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other

lbendlin — Sat, 22 Jun 2024 15:36:13 GMT

You will have to create three calculated tables, either materialized or as part of the measure (there might be severe performance issues in the latter approach, especially with your amount of data). You need to start from the lowest level as I showed, and then need to use the result of that for the next level, and then the result of that for the top level.

Certainly doable but rather complex. Think about softening your requirement, or finding a graphical solution instead (like a decomposition tree)

Re: Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other

TFRec — Sat, 22 Jun 2024 19:06:39 GMT

Hello qqqqqwwwweeerrr ,

Thank you so much for this. I just had to modify it a little bit and it works perfectly. I was really overcomplicating it with my use of nested TOPNs with KEEPFILTERS.

Top 3 multiple test = VAR RankCode = RANKX( ALLSELECTED(Table[Product_code]),[Sum Euros All Time],,DESC,Dense ) VAR RankOrigin = RANKX( ALLSELECTED(Table[Origin_COuntry]),[Sum Euros All Time],,DESC,Dense ) VAR RankDestination = RANKX( ALLSELECTED(Table[Destination_Country]),[Sum Euros All Time],,DESC,Dense ) VAR RankAll = SWITCH( TRUE, ISINSCOPE(Table[Destination_Country]),RankDestination, ISINSCOPE(Table[Origin_Country]),RankOrigin, ISINSCOPE(Table[Product_code]),RankCode ) RETURN IF( RankAll <= 3, [SumEuros],BLANK() )

Re: Nested (3 levels) TOPN using Power BI DAX not working for categories but working for other

TFRec — Sat, 22 Jun 2024 19:10:25 GMT

Hello lbendlin ,

Thank you very much for the help. I fear creating new tables I would impede a lot of the dynamic use of slicers, filters, etc. on the report unless I went the route of using new tables just for the list of values and then still dynamically calculate the values associated to them (which is doable), so I ended up using the approach indicated by another user.