Dynamic Top N + Other filtering

[danimelv]

Hi All,

I've been playing a while but I can't get what I want, so I am asking for help, pls

Attached you can find the PBIX File . I have a measure that returns the Total Amount of Top 3 Countries and the rest of countries:

However, when the users click on "Others" I woould like to retrieve the other countries with the amount, that is, Poland, Portugal and Spain with 9,8,5 Total respectively:

I have created an Unlinked table in order to make a virtual relation and create a measure to calculate the total for this countries. Unfortunately, I'm not able to match the Country from my Table with my new Unlinked table:

DEFINE
    VAR Tab1 =
        ADDCOLUMNS (
            SUMMARIZE ( CountryOthers, CountryOthers[Ctry] ),
            "Actuals", [Total],
            "@Others", [Amount Oth]
        )
    VAR Tab2 =
        FILTER ( Tab1, AND ( [Actuals] <> BLANK (), [@Others] = BLANK () ) )
    VAR Tab3 =
        SUMMARIZE ( Tab2, [Ctry] )
EVALUATE   
ADDCOLUMNS (
    SUMMARIZE ( Unlink, Unlink[Ctry] ),
    "TotalOthers",
        SUMX (
            Tab3,
            VAR CurrCtry = [Ctry]
            RETURN
                CALCULATE ( [Total], CountryOthers[Ctry] = CurrCtry )
        )
)

Any Idea how can achieve this? Maybe is there a better way to approach this problem?

File 

[danimelv · Accepted solution]

Hi @TheoC 

Ofc I've tried, but maybe it would be easier if you share your solution (for sure I'm doing something wrong but It did not work as spected). Anyway, I've found the solution to the requirement so I'm sharing it in case anybody has the same requirement:

VAR OthersSel = SELECTEDVALUE( CountryOthers[Ctry] ) = "Others"
VAR Tab1 =
    ADDCOLUMNS (
        ALL ( CountryOthers[Ctry] ),
        "@Others", [Amount Oth],
        "Actuals", [Total]
    )
VAR Tab2 =
    FILTER ( Tab1, AND ( [Actuals] <> BLANK (), [@Others] = BLANK () ) )
VAR Tab3 =
    SUMMARIZE ( Tab2, [Ctry] )
VAR Result =
SUMX (
            INTERSECT ( Tab3, Unlink ),
            VAR _curr = [Ctry] RETURN CALCULATE ( [Total], Tabla[Ctry] = _Curr )
        )
Return
IF( OthersSel, 
    Result,
    CALCULATE( [Total], TREATAS(VALUES(Unlink[Ctry]), CountryOthers[Ctry]) ) 
)

[danimelv]

@TheoCI have tried but as I mentioned, it did not work for me. Maybe, if you could attach a sample file it would be easier (for sure I am doing something wrong). Anyway, I have found the solution via formula, so I put it here if someone has the same requierment:

Amount_Unlink = 
    VAR Tab1 =
        ADDCOLUMNS (
            VALUES ( CountryOthers[Ctry] ),
            "@Others", [Amount Oth],
            "Actuals", [Total]
        )
    VAR Tab2 =
        FILTER ( Tab1, AND ( [Actuals] <> BLANK (), [@Others] = BLANK () ) )
    VAR Tab3 =
        SUMMARIZE ( Tab2, [Ctry] )
Return
SUMX (
            INTERSECT ( Tab3, Unlink ),
            VAR _curr = [Ctry] RETURN CALCULATE ( [Total], Tabla[Ctry] = _Curr )
        )

[TheoC]

@danimelv as long as you honestly tried it, that's what matters.

[TheoC]

Edited:

Hi @danimelv 

I've done something similar but taken a bit more of a "two step approach".  

1. Create a new table using your Country names:

New Table = SUMMARIZE ( 'Table' , 'Table'[Country Name] )

2. Create Calculated Column in New Table to rank / group:

New Column1 = 

VAR _1 = RANKX ( ALL ( 'Table' ) , [Your Measure] , , DESC )
RETURN
IF ( _1 <= 3 , 'New Table'[Country Name] , "Other" )

Ensure to create a 1 to many relationship from New Table[Country Name] to your original table and you can then drag the New Column1 into the Matrix or Table visual you're using.

Hope this helps!

Theo 🙂

[TheoC]

@danimelv apologies mate - the error was on my part. I had the RANKX ( ALL ( "New Table" ) ... instead of original table! Definitely not something you were doing wrong mate!  That was on me!

[danimelv]

Hi @TheoC Thanks for your response. The only problem I can see from your solutions is, What if the Top N is dynamic? e.g, If I have a date filter, the Top N could vary depending on the date selected. That's what I've been thinking on doing with an unlinked table and not creating a physical relation. 🙂

[TheoC]

@danimelv The solution I provided is dynamic and takes that into account. I use it all the time on frequently updated data. 

[danimelv]

@TheoCThat's the think. The rank could change whenever a filter e.g of date is selected, not when the data is refreshed. Tha is why I need to be completely dynamic taking into account all the possible filters the users may choose.

[TheoC]

@danimelv @Yes, it takes that into account. Have you tried it or are you just making assumptions at the moment?