Top n

[badger123]

Hello,

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

Table 1
Date	Phrase	Value
Jan-19	phrase 1	10
Jan-19	phrase 2	20
Jan-19	phrase 3	30
Jan-19	phrase 4	40
Jan-19	phrase 5	70
Feb-19	phrase 1	100
Feb-19	phrase 2	30
Feb-19	phrase 3	20
Feb-19	phrase 4	10
Feb-19	phrase 5	5
Mar-19	phrase 1	150
Mar-19	phrase 2	20
Mar-19	phrase 3	30
Mar-19	phrase 4	40
Mar-19	phrase 5	200

Table 2
Phrase	Legend
phrase 1	Category A
phrase 2	Category B
phrase 3	Category B
phrase 2	Category C
phrase 4	Category C
phrase 5	Category D
phrase 3	Category 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. 

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: 

Date	Top 2	Total Value
Jan-19	Category D	70
Jan-19	Category C	60
Feb-19	Category A	100
Feb-19	Category B	50
Mar-19	Category D	200
Mar-19	Category A	150

[v-juanli-msft · Accepted solution]

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

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)

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.

[Anonymous]

@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

[badger123]

Hi @Anonymous , 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). 

[Anonymous]

@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]

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

[Anonymous]

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