Dynamics TopN filtered by an other column

[Vahks]

Hello,

I'm trying to find a solution but nothing helps me.

So, I would like to have a dynamic topN which brings together the following list of artists :

This is correctly filtered when i use this slicer :

With these measures  :

Tickets = IF([Rank Artist] <= [TopN Value], [Sum_Tickets])

Sum_Tickets = SUM(Events[Ticket_Sold])

Rank Artist = RANKX(
       ALL(PollStar_events_FilteredSearch[Artist_Name]),
       [Tickets], , DESC, Dense)

The table of artists changes if I take the topN as follows:

But I want to keep the top 10 artists and filter it according to the list of systems below: (i have to hide the system names, but you can call it System 1, System 2, etc..) :

But when I move the TopN cursor, it doesn't work and it keeps the number of tickets for all the artists whether or not they are part of the TopN entered.

Does anyone have an idea how I could achieve this result please ?

If I'm not clear, don't hesitate to ask me for more information.

[parry2k]

@Vahks I have no idea what you are doing here. The measure you shared is not the same as what I sent, so not going to look into it until you follow the instructions as mentioned. Thanks!

[Vahks]

@parry2kSorry, now it's work, i have an other filter which created problem, now your solution work perfectly, thank very much !

[parry2k]

@Vahks and Sum Tickets is just a measure:

Sum Tickets = SUM ( 'DataTable'[Tickets] )

[parry2k · Accepted solution]

@Vahks try this measure, and here is the visual:

TopN Tickets = 
VAR __TopN = 5  --replace this value of 5 with your TopN selected value
VAR __RankTableByArtist = 
    ADDCOLUMNS ( 
        ALLSELECTED ( 'DataTable'[Artist_Name] ), 
        "@Rank", 
            CALCULATE ( 
                RANKX ( ALLSELECTED ( 'DataTable'[Artist_Name]  ), [Sum Tickets], , DESC ), 
                REMOVEFILTERS ( 'DataTable'[System] ) 
            ) 
    ) 
VAR __FilterTopN = FILTER ( __RankTableByArtist, [@Rank] <= __TopN ) 
RETURN
CALCULATE ( 
    [Sum Tickets], 
    KEEPFILTERS ( __FilterTopN ) 
)

Result based on Top 5

Result based on Top 7

[Vahks]

Thank you, I think we are close to the result but there is a difference in result and I don't understand why !

When i selected top 2, you can see the TopN Tickets measure have no result :

 

When I remove the remove filter from your function

VAR __RankTableByArtist = 
    ADDCOLUMNS ( 
        ALLSELECTED ( 'DataTable'[Artist_Name] ), 
        "@Rank", 
            CALCULATE ( 
                RANKX ( ALLSELECTED ( 'DataTable'[Artist_Name]  ), [Sum Tickets], , DESC )
            ) 
    ) 

The sum is consistent with the ranking of the artists but obviously it no longer filters through the system.

 

Can I send you my report so you can maybe see better what's going on?

[parry2k]

@Vahks can you clarify what you mean by "systems" column? It is not clear what you are trying to achieve.

Do you want to first find out who are top 10 artists and then show all the systems for those artists? What does your model look like? Also, share sample data in the table format with the expected output.

[Vahks]

Hello @parry2k ,

Systems are just sort of a category, I just have a table that could be represented this way, :

No I don't need to see the top artists, I only want the sum of tickets belonging to the chosen top artist (it can be the top 10, 20 or 50, etc.) to be placed under the different categories system.

example with the data above if i want the top 7 artist by System :

Here is in detail where the sum of the Tickets column comes from (I don't need this table, it's just to show what the figures are made of) :

example with the data if i want the top 5 artists by system :

Rank 6 and 7 are deleted from system amounts 1 and 2 because Artist 1 uses system 1 and artist 2 uses System 2 and are not part of the top 5.

hope it's more clearer