cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
badger123 Member
Member

Top n

Hello,

 

Does anyone know how to achieve top n by date? More details below...

 

Table 1  
DatePhraseValue
Jan-19phrase 110
Jan-19phrase 220
Jan-19phrase 330
Jan-19phrase 440
Jan-19phrase 570
Feb-19phrase 1100
Feb-19phrase 230
Feb-19phrase 320
Feb-19phrase 410
Feb-19phrase 55
Mar-19phrase 1150
Mar-19phrase 220
Mar-19phrase 330
Mar-19phrase 440
Mar-19phrase 5200

 

Table 2 
PhraseLegend
phrase 1Category A
phrase 2Category B
phrase 3Category B
phrase 2Category C
phrase 4Category C
phrase 5Category D
phrase 3Category E

 

Current approach: Tables 1 and 2 are connected by phrase. I am using the categories as the legend and summed values as the value. I'm currently using a top n filter (e.g. top 2) which is showing the top 2 based on total value over the entire period. 

 

Ribbon Chart.PNG

 

Desired output: What I'm trying to do is show the top n at each given date. So based on the sample of tables I shared, this would look something like: 

 

DateTop 2Total Value
Jan-19Category D70
Jan-19Category C60
Feb-19Category A100
Feb-19Category B50
Mar-19Category D200
Mar-19Category A150
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Top n

Hi @badger123 

1.Create measures in Table2

Measure_value = SUM('Table 1'[Value])

rank = RANKX(ALL('Table 2'[Legend]),[Measure_value],,DESC)

2.Then create a table by entering data in Modeling menu

11.png

Don't create any relationship for this table

add "top" column from this table into a slicer

 

3.Create measures in Table2

selected topn = SELECTEDVALUE('top_n table'[top])

flag = IF('Table 2'[rank]<='Table 2'[selected topn],1,0)

10.png

Add "flag" measure in the visual level filter of that table as above

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

6 REPLIES 6
natelpeterson New Contributor
New Contributor

Re: Top n

@badger123 - Just to clarify, a single phrase has multiple Categories. This could be problematic - could you share a screenshot of your model diagram (relationships)? 

Thanks,

Nathan

natelpeterson New Contributor
New Contributor

Re: Top n

@badger123 - One potential solution is to add a new Calculated Table:

Top Legends = 
var x = SUMMARIZE(Table1, Table2[Legend], Table1[Date], "TotalValue", sum(Table1[Value]))
var y = ADDCOLUMNS(
            x, 
            "Rank", 
            RANKX(
                FILTER(
                        x,
                        [Date]=EARLIER([Date])
                ),
                [TotalValue]
            )
        )
var z = FILTER(y, [Rank] <= 2)
return z
badger123 Member
Member

Re: Top n

Hi @natelpeterson , thanks for your help. Yes, a single phrase can have more than one category assigned to it... The tables are linked by phrase (many to many). 

badger123 Member
Member

Re: Top n

@natelpeterson  I tried the measure, but keep getting : The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

natelpeterson New Contributor
New Contributor

Re: Top n

@badger123  - That's for a calculated table, not a measure.

Community Support Team
Community Support Team

Re: Top n

Hi @badger123 

1.Create measures in Table2

Measure_value = SUM('Table 1'[Value])

rank = RANKX(ALL('Table 2'[Legend]),[Measure_value],,DESC)

2.Then create a table by entering data in Modeling menu

11.png

Don't create any relationship for this table

add "top" column from this table into a slicer

 

3.Create measures in Table2

selected topn = SELECTEDVALUE('top_n table'[top])

flag = IF('Table 2'[rank]<='Table 2'[selected topn],1,0)

10.png

Add "flag" measure in the visual level filter of that table as above

 

Best Regards
Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.