Create anew column

[Ranya]

I have adata included two tables 

Customer table included customer ID, customer name, state, city, country .

Orders table included order ID, sales, profits, customer ID, date .

I want to create a new column caculated the most 5 customers having sales .

[Ranya · Accepted solution]

I have adata included two tables 

Customer table included customer ID, customer name, state, city, country .

Orders table included order ID, sales, profits, customer ID, date .

I want to create a new column caculated the most 5 customers having sales .

[Emantaha85 · Accepted solution]

First,you need to Create a relationship between the Customer and Orders tables using the customer ID column.

**Then create a measure for total sales:

Total Sales = SUM(Orders[sales])

and to rank customers by sales, you can use the following DAX measure:

Customer Sales Rank = 

RANKX(

    ALL(Customer),

    [Total Sales],

    DESC)

To create the new column identifying the top 5 customers, you can add a calculated column to the Customer table:

Top 5 Customer = 

IF(

    [Customer Sales Rank] <= 5,

    "Top 5 Customer",

    "Other")

To visualize this data:

Select a bar chart  vidual with Customer[customer_name] on the  x axis and [Total Sales] as the value in y axis

Add a visual level filter to the chart to only show where [Top 5 Customer] is "Top 5 Customer".

This will give you a bar chart of the top 5 customers by sales.

[Ranya · Accepted solution]

I have adata included two tables 

Customer table included customer ID, customer name, state, city, country .

Orders table included order ID, sales, profits, customer ID, date .

I want to create a new column caculated the most 5 customers having sales .

[gmsamborn]

Hi @Ranya 

I'm not sure if you found a solution.

If not, here is a calculated column for your Customer table:

Top 5 Sales CC = 
VAR _Cust = [CustomerID]
VAR _Table1 =
    SUMMARIZE(
        ALL( 'Orders' ),
        Orders[CustomerID],
		"__Sales",
	        SUM( Orders[Sales] )
    )
VAR _Table2 =
    ADDCOLUMNS(
        _Table1,
        "__Rank",
            RANK(
				DENSE,
				_Table1,
				ORDERBY( [__Sales], DESC )
			)				
    )
VAR _Table3 =
	ADDCOLUMNS(
		_Table2,
		"__Top5Sales",
			IF( [__Rank] < 6, [__Sales], 0 )
	)
VAR _Result =
    MAXX(
        FILTER(
            _Table3,
            [CustomerID] = _Cust
        ),
        [__Top5Sales]
    )
RETURN
    _Result

If you have any questions, let me know.

Calculate top customer column.pbix