Forum Discussion

Ranya's avatar
Ranya
Frequent Visitor
2 years ago
Solved

Create anew column

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 ...
Show and Tell
  • Ranya's avatar
    Ranya
    2 years ago

    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's avatar
    Emantaha85
    2 years ago

    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's avatar
Ranya
Frequent Visitor
2 years ago

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's avatar
    gmsamborn
    Super User
    2 years ago

    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