Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I'm trying to rank customers by the SUM (Total POS $) and am having difficulty getting Rankx to work as I would like it to. I have the following table: In this case, what I would like to do would be to aggregrate Total POS $ for each pos_end_customer_name for the month. I would like to ignore for the time being business_group and vp_area_description. Example on Feb 1st, I would like to aggregate $ for Zyteq (197 + 32) and rank this against all other unique customers throughout the rest of the month. I've tried the following:
Thanks!
Matthew
You may try below calculated column:
Column = RANKX ( FILTER ( salesdashboard_pos, salesdashboard_pos[pos_date] = EARLIER ( salesdashboard_pos[pos_date] ) ), CALCULATE ( SUM ( salesdashboard_pos[Total POS $] ), ALLEXCEPT ( salesdashboard_pos, salesdashboard_pos[pos_end_customer_name], salesdashboard_pos[pos_date] ) ), , DESC )
Regards,
Hello @v-cherch-msft and thank you for the input. The calculation that you provided gets us very close to the desired solution. What's happening is that we are ranking with a Skip by sum(Total POS $). If we change to Dense, that will solve the problem towards the top end of the list, where sum(Total POS $) is unique for each customer. We run into a problem towards the bottom of the order where many distinct customers share the same value of sum(Total POS $). I would like to see a ranking for distinct customers...which is where a Skip would make sense, but that doesn't work at the top of the list (Ex. Zyteq Technologies should have a rank of 3).
I kept at it with this...
Could you share some simplified sample data which could reproduce your scenario and your desired output?
Regards,
Can you please share the table in the screen capture in text-tabular format so that it can be readily copied? Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).
Here you are @AlB Thanks in advance.
pos_date | business_group | vp_area_description | pos_end_customer_name | Total POS $ | Total POS Q | Rank C | Rank Customers by Month | Rank Customers by Month by BG | Index |
Friday, February 1, 2019 | CBG | EUROPE | ZYTEQ TECHNOLOGIES | 70 | 632 | #ERROR | 26519 | 12694 | 1823291 |
Friday, February 1, 2019 | CBG | EUROPE | ZYTEQ | 197 | 2160 | #ERROR | 19180 | 9143 | 1823289 |
Friday, February 1, 2019 | FEBG | EUROPE | ZYTEQ | 32 | 210 | #ERROR | 32680 | 8170 | 1823290 |
Friday, March 1, 2019 | CBG | AMERICAS | ZYNEX MEDICAL INC | 17 | 400 | #ERROR | 23955 | 12577 | 1856519 |
Friday, February 1, 2019 | CBG | AMERICAS | ZYNEX MEDICAL | 563 | 20000 | #ERROR | 12414 | 5920 | 1823287 |
Friday, March 1, 2019 | CBG | AMERICAS | ZYNEX MEDICAL | 15 | 370 | #ERROR | 24527 | 12915 | 1856518 |
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |