DAX code to filter topn

[Ang0zyx]

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 SalesTable
where product in
(SELECT distinct product as top3product FROM (SELECT product,sales FROM SalesTable ORDER BY sales)

WHERE ROWNUM<=3)

I tried with below dax but is not working.when I add product and sales to a visual,it's properly showing 3 top products but on adding country, it shows all rows.

Calculate(sum(sales),keepfilters(topn(3,allselected(SalesTable[product]),sum(sales),desc)))

[Ang0zyx]

Could anyone please help on this issue.

[Ang0zyx]

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

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

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 ()
)

[Ang0zyx]

Hi @Jihwan_Kim , Thanks a lot . It worked perfectly 😀

[Ang0zyx]

Hi @Jihwan_Kim , It is not working when i add both the dimension in the Visual. Please find the screenshot below.

Top 3 Product Sales =

CALCULATE (

SUM ( SalesTable[Sales] ),

KEEPFILTERS (

TOPN (

3,

ALLSELECTED ( SalesTable[Product] ),

CALCULATE ( SUM ( SalesTable[Sales] ) ), DESC

)

)

)

Note:

1. I cannnot use visual filter TopN for some certain reason.
2. the data is in a single table like the Screenshot.

[Jihwan_Kim]

Hi,

Thank you for your feedback.

Please check the below picture and the attached pbix file.

All measures are in the attached pbix file.

Total sales measure: = 
SUM(Sales[Total Sales])

Top3 product sales measure: = 
VAR topthreeproducts =
    TOPN ( 3, ALL ( 'Product'[Product] ), [Total sales measure:], DESC )
RETURN
    CALCULATE (
        [Total sales measure:],
        KEEPFILTERS (
            TOPN ( 3, ALL ( 'Product'[Product] ), [Total sales measure:], DESC )
        )
    )

top3 product sales measure fix: = 
IF (
    CALCULATE ( [Top3 product sales measure:], REMOVEFILTERS ( Country[Country] ) )
        = BLANK (),
    BLANK (),
    [Top3 product sales measure:]
)

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like or how your desired outcome looks like, but please try the below.

new measure: =
CALCULATE (
    SUM ( SalesTable[sales] ),
    KEEPFILTERS (
        TOPN (
            3,
            ALLSELECTED ( SalesTable[product] ),
            CALCULATE ( SUM ( SalesTable[sales] ) ), DESC
        )
    )
)