Forum Discussion

Rocky_Brown's avatar
Rocky_Brown
Icon for Helper I rankHelper I
4 years ago
Solved

Top 10 Customers excluding one Customer Name

I am trying to Obtain the Top 10 Customers in Sales with the exclusion of 1 Customer.

 

I have this so far, but getting an error.

 

Top 10 Customers CY Month =
CALCULATE ([Sales],
FILTER(VALUES( IF('TableName'[CustomerName] <> "ABCXYX",
rankx(ALL('TableName'[CustomerName], 'TableName'[CustomerID] ), [Sales],,Desc)<= 10,[Sales],BLANK() ))))
 
Please help.

 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    4 years ago

    Hi Rocky_Brown 

    Thanks for reaching out to us.

    if you want to create a table with top 10 customers, you can try this,

    sample data

    create the rank measure

    RANK = RANKX( SUMMARIZE(ALL('Table'),'Table'[CustomerID]) , CALCULATE( SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[CustomerID])),,DESC,Dense)

    create the top 10 table,

    Top 10 Customers CY Month = 
var _t=FILTER('Table',[RANK]<=10)
return DISTINCT(SELECTCOLUMNS(_t,"customer name",[CustomerName],"rank",[RANK]))
    Top 10 Customers CY Month =

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

    • Rocky_Brown's avatar
      Rocky_Brown
      Icon for Helper I rankHelper I
      4 years ago

      I am just tring to make it a measure, I am confused about making it a table.  When I use your code for a measure, I am getting an error. "The expression refers to Multiple Columns.  Multiple Columns cannot be converted to a scalar value".

       

      Thanks

       

      • ryan_mayu's avatar
        ryan_mayu
        Icon for Super User rankSuper User
        4 years ago

        Creating a table is a workaround for you. let's see if any expert can create a measure for this.

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi Rocky_Brown 

    Thanks for reaching out to us.

    if you want to create a table with top 10 customers, you can try this,

    sample data

    create the rank measure

    RANK = RANKX( SUMMARIZE(ALL('Table'),'Table'[CustomerID]) , CALCULATE( SUM('Table'[Sales]),ALLEXCEPT('Table','Table'[CustomerID])),,DESC,Dense)

    create the top 10 table,

    Top 10 Customers CY Month = 
var _t=FILTER('Table',[RANK]<=10)
return DISTINCT(SELECTCOLUMNS(_t,"customer name",[CustomerName],"rank",[RANK]))
    Top 10 Customers CY Month =

     

    Best Regards,

    Community Support Team _Tang

    If this post helps, please consider Accept it as the solution to help the other members find it more quickly.