Top 10 Sales Only

[DW868990]

Hi, 

I am looking for some help as I am struggling to get the DAX correct - 

I am needing to make a visualisation which simply shows 2 measures:

Top 10 Sales by Customer

All Other Customers

I am struggling the top N concept to show simply the grand total of the top 10 highest sales by customer, after this i could then subtract from all sales. 

Any help with the DAX appreciated 🙂

[DW868990]

Thanks but I am afraid not, what I am looking for is a dynamic measure that returns a table that looks like the below. 

[PabloDeheza · Accepted solution]

Okay, so, I implemented a solution. Hope this helps.

First you will need an auxiliar table in order to show Top 10 and Others in a visual, the DAX for your table looks like this:

Top Customers = 
{ "Top 10 Customers", "Others" }

Then you will create a Ranking measure like the one below:

Ranking = 
RANKX(
    ALL( Customer[Name] ), 
    [Sales]
)

 Then you will need your final measure which is the one that splits the ranking:

Top 10 & Others = 
VAR _Rank =
    ADDCOLUMNS(
        ALL( Customer[Name] ),
        "@Sales", [Sales],
        "@Rank", [Ranking]
    )
VAR _Top10 =
    SUMX(
        FILTER(
            _Rank,
            [@Rank] <= 10
        ),
        [@Sales]
    )
VAR _Others =
    SUMX(
        FILTER(
            _Rank,
            [@Rank] > 10
        ),
        [@Sales]
    )
VAR _Result =
    SWITCH(
        SELECTEDVALUE( 'Top Customers'[Value] ),
        "Top 10 Customers", _Top10,
        "Others", _Others,
        BLANK()
    )
RETURN
    _Result

Last step, drag what you need into a table, that is "Value" from your newly created table ( you can name it whatever you need, by default will be "Value" ) and your "Top 10 & Others" measure. The result should look like this:

I hope this helps solving your requirement!

[DW868990]

Works great! Thank you 🙂

[PabloDeheza]

Hi there!

You can refer to this post that shows how to implement a top N

https://radacad.com/top-n-filter-in-power-bi-with-zero-dax-code-visual-level-filter

Let me know if that helps!