Forum Discussion
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 new column caculated the most 5 customers having sales .
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 .
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.
3 Replies
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 .
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 _ResultIf you have any questions, let me know.
Calculate top customer column.pbix
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.
