Calculate top 6 clients in descending order

[gra_197]

Hi

I am hoping you can help me.

I need to get a list of top 6 clients based on the highest sales volume

Secondly I want to get an average of the volume of these clients over the total client volume by sales

Thanks

G

[v-hashadapu]

Hi , Thank you for reaching out to the Microsoft Community Forum.

To help you identify your top clients and measure their impact on your overall sales, we built a Power BI report using two tables based on the details provided by you: Customers and Sales, connected by a relationship on CustKey. We created DAX measures to calculate total sales, the combined sales of your top 2 clients and the percentage those top clients contribute to the total. This gives you a clear view of who your highest-value clients are and what share of your revenue they represent.

Please check the attached .pbix file and share your thoughts.

[gra_197]

Hi all, just to give some context. Im looking for the top 2 Clients here based on sales and want to get the percentage based on the overall volume and sales amount

@FBergamaschi 

 

 

[FBergamaschi · Accepted solution]

Thanks for the @ so I would come back

Supponsing you have a Sales measure ( [Sales] )

Sales Top 2 =

VAR Top2Cust =

    TOPN( 2, ALL( Customer[Custkey] ), [Sales], DESC)

RETURN

CALCULATE( [Sales], KEEPFILTERS( Top2Cust) )

Sales Top 2 Pct = DIVIDE ( [Sales Top 2], [Sales] )

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[FBergamaschi]

@gra_197 

is mine or somebody else's answer a solution?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI

[Jihwan_Kim]

Hi, 

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

función WINDOW (DAX) - DAX | Microsoft Learn

Sales: = 
SUM( sales[sales] )

Sales top 6 clients: = 
CALCULATE (
    [Sales:],
    KEEPFILTERS (
        WINDOW (
            1,
            ABS,
            6,
            ABS,
            ALL ( customer[customer] ),
            ORDERBY ( [Sales:], DESC )
        )
    )
)

Average sales top 6 clients: = 
IF (
    [Sales top 6 clients:],
    AVERAGEX ( ALL ( customer[customer] ), [Sales top 6 clients:] )
)

[FBergamaschi]

Hi,

without tables, model etc the only thing we can do is give you the DAX code  of a generic measure for the Sales of the top 6 customers based on sales itself

Sales Top6 =

VAR Top=

    TOPN( 6, ALL( Cust[Custkey] ), [Sales], DESC)

RETURN

SUMX ( KEEPFILTERS(Top), [Sales] )

The second question in unclear to me, can you rephrase it and show an example?

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI