Forum Discussion
Community ChampionDAX to filter table based on TOPN aggregation
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.
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.⚡
5 Replies
- AlexisOlson
Super User
smpa01 ^^ This is exactly what I would have suggested.
- parry2k
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.⚡
CNENFRNLThis measure functions equally instead of visual level filter
Sales = SUMX(FILTER(VALUES('Table'[name]), [_rank]<3),[_sum])
