daxpowerbi-embedded

TOPN DAX function not working. The formula is returning all the rows and not TOP 3


My goal is to create measure to get top 3 customer Names and there respective sales.

I am using the below measure to bring top 3 names along with there sales. The below measure is returning all the rows. I fail to understand why this is happening and why filtering is not happening for top 3 customers

topN = calculate(sum(Sale[Total Excluding Tax]),
                 TOPN(3,
                      values(Sale[Employee Name]),
                      calculate(sum(Sale[Total Excluding Tax]))
                      )
                )

Sale[Employee Name] is calculated column and is coming from another table Employee by using Employee Name = RELATED(Employee[Employee])


Solution

  • The DAX is working properly and grabbing top 3 records. Order/sorting is important. You need to order your results.

    Create a calculate column [Total Excluding Tax] to sum up the Total excluding tax. Then use that column in a measure; try something like:

    Top Sales = TOPN ( 3, ALLSELECTED( 'Sale' ), [Total Excluding Tax]), desc)