Forum Discussion
DAX code to filter topn
Hi everyone, Could you please help me to convert the below query to dax.the idea is to filter the overall top3 products and show for all countries. SQL: Select country,product,sales from SalesT...
Hi Jihwan_Kim ,
Initially it worked but later on it failed with a single table. Please find the data,Measure & the issue below.
Total sales measure: =
SUM(Sales[Sales])
Top3 product sales measure: =
VAR topthreeproducts =
TOPN ( 10, ALL ( 'Sales'[Product] ), [Total sales measure:], DESC )
RETURN CALCULATE ([Total sales measure:],KEEPFILTERS (TOPN ( 10, ALL ( 'Sales'[Product] ), [Total sales measure:], DESC ) ) )
top3 product sales measure fix: =IF (CALCULATE ( [Top3 product sales measure:], REMOVEFILTERS ( Sales[Source] ) )
top3 product sales measure fix: =IF (CALCULATE ( [Top3 product sales measure:], REMOVEFILTERS ( Sales[Source] ) )
= BLANK (),BLANK (),[Top3 product sales measure:])
Issue: of the rows are missing when it is impleted through dax.
ProductSourceSales
| A66 | S1 | 56 |
| A77 | S1 | 29 |
| A169 | S1 | 29 |
| A223 | S1 | 18 |
| A230 | S1 | 1 |
| A231 | S1 | 1 |
| A232 | S1 | 3 |
| A247 | S1 | 29 |
| A301 | S2 | 2 |
| A301 | S1 | 84 |
| A303 | S3 | 5 |
| A303 | S2 | 4 |
| A303 | S1 | 25 |
| A712 | S3 | 21 |
| A712 | S2 | 4 |
| A712 | S1 | 28 |
| A718 | S2 | 1 |
| A729 | S3 | 18 |
| A729 | S1 | 11 |
| A767 | S1 | 33 |
| A769 | S4 | 1 |
| A783 | S3 | 15 |
| A783 | S1 | 34 |
| A794 | S3 | 27 |
| A794 | S1 | 7 |
- Jihwan_Kim
Super User
Hi,
I am not quite sure if I fully understand what your question is, but please check the below picture and the attached pbix file.
Sales total: = SUM( Data[Sales] )Sales by product: = CALCULATE( [Sales total:], ALLEXCEPT(Data,Data[Product]))Sales by product top10: = IF ( [Sales by product:] = CALCULATE ( [Sales by product:], KEEPFILTERS ( TOPN ( 10, ALL ( Data[Product] ), [Sales by product:], DESC ) ) ), [Sales total:], BLANK () )
