Forum Discussion
Solution Specialistitem wise dynamic customer rank
Hello
i have item table and customer table and quantity customer bought
i want to create dynamic rank accordingly to customer purchase in different items
| Customer 1 | Customer 2 | Customer 3 | Customer 4 | Customer 5 | Customer 6 | |
| Item A | 1 | 3 | 4 | 2 | 5 | 4 |
| Item B | 2 | 4 | 2 | 3 | 4 | 5 |
| Item C | 3 | 5 | 1 | 4 | 2 | 3 |
| Item D | 4 | 2 | 3 | 1 | 3 | 2 |
| Item E | 5 | 1 | 5 | 5 | 1 | 1 |
in this sample table Item A is mostly bought by Customer 1 so rank is 1 but item A is not mostly bought by customer 2 so for cusotmer 2 itsm A rank is 3
again Item E is mostly bough by customer 2, customer 5 and cusotmer 6 but not to others.
i want this dynamic item wise cusotmer item quantity purchase RANK in a dynamic way
can some one help please
thanks
Thanks for the replies from ahmedoye and Ashish_Mathur.
Hi abc_777,
Please try the following steps:
1. In Power Query Editor, select the first column and unpivot the other columns.
2. Modify column names and delete useless data.
3. Create a measure:
Rank = RANKX(ALLEXCEPT('Table','Table'[sub_category_name]),CALCULATE(SUM('Table'[sales in kg])),,DESC,Dense)4. Create a matrix:
Best Regards,
Zhu
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
PBI file attached.
Hope this helps.
7 Replies
- ahmedoye
Responsive Resident
Hi, this seems like your final solution right? I assume you have a measure already that counts or sums for each item? Now you need a ranking formula to provide the results as displayed in your attached image?
abc_777Hi ahmedoye ,
i have all those sum and count. I have rank but not like this dynamic way. item wise quantity purchase rank for each customer . it could happen that an item that customer 1 purchase a lot might not purchase to other customer. so i want to see the comparison
thanks
- Ashish_Mathur
Super User
Hi,
You have shared the end result (not the source data). Atleast share some data to work with.
- abc_777
Ashish_Mathur , here is item wise customer purchae quantty data
CUSTOMER_FIRST_NAME Customer A Customer B Customer C Customer D Customer E Customer F Customer G Customer H Customer I Customer J Customer K Customer L Customer M Customer N Customer O Customer P Customer Q Customer R Customer S Customer T sub_category_name sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg sales in kg BEEF BONE IN 1276.00 1430.00 0.00 4.00 1755.00 774.00 81.00 117.00 123.00 77.00 32.00 BEEF BY PRODUCT 20.00 15.30 75.00 BEEF BY PRODUCT CARCASS 22.00 BEEF COLD CUTS 4.00 2592.00 32.40 15.00 0.60 8.00 BEEF FROZEN PACKET 0.63 BEEF MEAT TRIM 56.00 10.00 35049.40 16.00 4.00 4.00 80.00 BEEF MEAT TRIM MARINATION 502.70 0.00 Beef Primal 58.30 6.40 5.30 124.85 16.10 151.10 0.20 7.00 2.00 Beef Sub-Primal 246.30 17.80 526.90 63.50 3.90 10.00 4.00 0.00 3.70 16.80 CHICKEN BONE IN 37.00 1670.00 0.00 CHICKEN BONELESS&PRIMALS MARIN 0.00 CHICKEN COLD CUTS 50.00 40.00 54.00 3.00 CHICKEN FROZEN PACKET 1.26 0.54 CHICKEN MEAT TRIM MARINATION 52.30 12.80 0.00 4.60 Chicken Primal 559.00 8738.00 72.00 13724.00 4598.00 10631.00 1630.00 7427.00 12005.00 85.00 Chicken Sub-Primal 10.00 0.00 1708.00 89.00 2.00 21.00 10.00 FISH BONELESS 6.00 1.00 Marinated Fish 0.00 MUTTON BONE IN 65.00 0.00 829.00 0.00 10.00 MUTTON BY PRODUCT CARCASS 149.00 - Ashish_Mathur
Hi,
PBI file attached.
Hope this helps.
Thanks for the replies from ahmedoye and Ashish_Mathur.
Hi abc_777,
Please try the following steps:
1. In Power Query Editor, select the first column and unpivot the other columns.
2. Modify column names and delete useless data.
3. Create a measure:
Rank = RANKX(ALLEXCEPT('Table','Table'[sub_category_name]),CALCULATE(SUM('Table'[sales in kg])),,DESC,Dense)4. Create a matrix:
Best Regards,
Zhu
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- abc_777
thanks buddy, the formula u gave didnt work but some how i get the concept and able to made that as my tables for item and customers are different. anyways. it works. thanks again. cheers
