DAX to filter table based on TOPN aggregation

[smpa01]

I have a table like this

| name | sale |
|------|------|
| a    | 1000 |
| a    | 1500 |
| b    | 2000 |
| b    | 1500 |
| c    | 2000 |
| c    | 2000 |
| d    | 5000 |

and I am trying to write a measure to utilize TOPN that would return the filter table based on TOP 2 sale by name.

I want to end up with this

| name | sale |
|------|------|
| d    | 5000 |
| c    | 4000 |

The equivalent SQL is following

declare @t1 as table (name varchar(1),sale int)
insert into @t1
select * from
(values('a',1000),('a',1500),('b',2000),('b',1500),('c',2000),('c',2000),('d',5000)) t(a,b)

;with cte1 as(
select name, SUM(sale) as sale
from @t1
group by name
)
select TOP 2 * from cte1
order by sale desc

But in DAX if I try the following

Measure =
CALCULATE (
    SUM ( 'Table'[sale] ),
    CALCULATETABLE (
        'Table',
        TOPN ( 2, VALUES ( 'Table'[name] ), CALCULATE ( SUM ( 'Table'[sale] ) ), DESC )
    )
)

it does not do anything

However, I can get to where I want to by using the following set up

But I am simply trying to undertand, if it is possible to filter the table using TOPN.

@AlexisOlson  @CNENFRNL 

[CNENFRNL]

This measure functions equally instead of visual level filter

Sales = SUMX(FILTER(VALUES('Table'[name]), [_rank]<3),[_sum])

[parry2k]

@smpa01 you can also do this:

Top Name = 
CALCULATE ( 
    [Sum Sales], 
    KEEPFILTERS (
        TOPN ( 
            2, 
            ALLSELECTED ('Top'[name] ), 
            [Sum Sales], 
            DESC 
        )
    )
    //VALUES ('Top'[name] ) 
) 

[AlexisOlson]

@smpa01 ^^ This is exactly what I would have suggested.

[parry2k · Accepted solution]

@smpa01 here it is, tweak it as you see fit

Top Name = 
CALCULATE ( 
    [Sum Sales], 
    TOPN ( 
        2, 
        ALLSELECTED ('Top'[name] ), 
        [Sum Sales], 
        DESC 
    ), 
    VALUES ('Top'[name] ) 
) 

✨ Follow us on LinkedIn

Learn about conditional formatting at Microsoft Reactor

My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[smpa01]

@parry2k  this is extremely elegant and smooth !!! Thanks for this one.