CODEWITHSUNDEEP
daxpowerpivot
Leo Sam
Leo Sam

Reputation: 83

TOP 10 Values in DAX Aggregation

I want to show top 10 customers with highest sales amount in DAX. please let me know why this formula doesn't work, and what is the best syntax?!

DEFINE
MEASURE InvoiceLines[Sales] = sumx(InvoiceLines,InvoiceLines[Quantity]*InvoiceLines[UnitPrice])
EVALUATE

TOPN(10,
SUMMARIZE(
    Customers,
    Customers[customerID],Customers[CustomerName],
    "Sales" ,InvoiceLines[Sales]
    )
, InvoiceLines[Sales],DESC
)

Upvotes: 0

Views: 7315

Answers (1)

StelioK
StelioK

Reputation: 1781

There are several ways of achieving this; the easiest is to create a simple measure to calculate sales amount in DAX:

Total Sales := CALCULATE(SUM('Table'[Sales]))

Then you can simply use this in the "Top N" filtering option on the "Visual Level Filters" pane in the .pbix, you simply drop in the measure and choose 10 as your N parameter.

A more complicated way that I've done it in the past, using DAX:

Top 10 Customers :=
VAR RankingContext =
    VALUES ( 'Customers'[CustomerName] )
VAR TopNumber = 10
RETURN
    CALCULATE (
        [Total Sales],
        TOPN ( TopNumber, ALL ( 'Customers'[CustomerName] ), [Total Sales] ),
        RankingContext
    )

I hope this helps!! (I'd go with option #1)

Upvotes: 1

Related Questions