Forum Discussion
Need help with dynamic ranking using dax
vendor | group | year | invoice # | spend |
1002 | A | 2023 | 10001 | $ 3,275,404.00 |
1002 | A | 2023 | 10002 | $ 3,919,979.00 |
1002 | B | 2022 | 10003 | $ 3,320,651.00 |
1002 | B | 2023 | 10004 | $ 2,385,653.00 |
1002 | B | 2023 | 10005 | $ 2,158,321.00 |
1002 | B | 2023 | 10006 | $ 4,246,098.00 |
1002 | B | 2022 | 10007 | $ 4,693,622.00 |
1005 | A | 2023 | 10008 | $ 1,648,983.00 |
1005 | A | 2023 | 10009 | $ 2,403,157.00 |
1005 | A | 2022 | 10010 | $ 444,527.00 |
1005 | A | 2023 | 10011 | $ 1,075,092.00 |
1005 | C | 2023 | 10012 | $ 4,355,503.00 |
1005 | C | 2023 | 10013 | $ 817,127.00 |
1005 | C | 2022 | 10014 | $ 797,043.00 |
1005 | B | 2023 | 10015 | $ 304,520.00 |
1005 | B | 2023 | 10016 | $ 471,860.00 |
1005 | B | 2022 | 10017 | $ 1,823,371.00 |
2007 | C | 2023 | 10018 | $ 2,242,889.00 |
2007 | C | 2023 | 10019 | $ 1,974,326.00 |
2007 | C | 2022 | 10020 | $ 1,698,917.00 |
2007 | C | 2023 | 10021 | $ 1,578,562.00 |
2007 | C | 2023 | 10022 | $ 142,124.00 |
2007 | C | 2023 | 10023 | $ 1,233,712.00 |
2007 | C | 2022 | 10024 | $ 3,520,975.00 |
2007 | C | 2022 | 10025 | $ 1,833,172.00 |
2007 | C | 2023 | 10026 | $ 2,468,742.00 |
2007 | C | 2022 | 10027 | $ 2,074,428.00 |
2007 | C | 2023 | 10028 | $ 2,011,873.00 |
2007 | C | 2023 | 10029 | $ 3,870,067.00 |
5006 | A | 2023 | 10030 | $ 686,474.00 |
4007 | A | 2022 | 10031 | $ 307,452.00 |
2027 | A | 2022 | 10032 | $ 655,701.00 |
2027 | C | 2023 | 10033 | $ 557,174.00 |
2027 | C | 2022 | 10034 | $ 63,068.00 |
2027 | C | 2023 | 10035 | $ 676,519.00 |
2027 | B | 2023 | 10036 | $ 325,517.00 |
2027 | B | 2023 | 10037 | $ 437,127.00 |
4007 | A | 2022 | 10038 | $ 535,736.00 |
4007 | A | 2022 | 10039 | $ 351,313.00 |
4007 | A | 2023 | 10040 | $ 547,303.00 |
5006 | C | 2022 | 10041 | $ 754,842.00 |
5006 | C | 2023 | 10042 | $ 271,665.00 |
5006 | C | 2023 | 10043 | $ 359,705.00 |
5006 | B | 2023 | 10044 | $ 75,995.00 |
5006 | B | 2022 | 10045 | $ 198,562.00 |
5006 | C | 2022 | 10046 | $ 790,911.00 |
5006 | B | 2023 | 10047 | $ 592,415.00 |
2027 | B | 2022 | 10048 | $ 591,265.00 |
4007 | A | 2023 | 10049 | $ 516,936.00 |
Using the above data set:
I want to get a measure that tells me the net spend for each vendor, for each group, for the year 2023. I then want to rank them by net spend. And then I want to classify them into two groups. Vendors that make up the top 80% of spend, and vendors that make up the bottom 20% of spend, based on net 2023 spend.
Any help on how to do this so that it calculates dyanmically and I can choose whether or not I want to filter by group?
NOTE: I have millions of rows of invoices.
Hi,
Please check the below picture and the attached pbix file.
Spend: = SUM ( Data[spend] )Rank: = RANKX ( ALL ( vendor[vendor] ), [Spend:],, DESC )spend cumulate classify = VAR _allspend = CALCULATE ( [Spend:], ALL ( vendor[vendor] ) ) VAR _cumulatespend = CALCULATE ( [Spend:], WINDOW ( 1, ABS, 0, REL, ADDCOLUMNS ( ALL ( vendor[vendor] ), "@spend", [Spend:] ), ORDERBY ( [@spend], DESC ) ) ) VAR _percentage = DIVIDE ( _cumulatespend, _allspend ) RETURN IF ( HASONEVALUE ( vendor[vendor] ), IF ( _percentage <= 0.8, "top80%", "bottom20%" ) )
2 Replies
- Jihwan_Kim
Super User
Hi,
Please check the below picture and the attached pbix file.
Spend: = SUM ( Data[spend] )Rank: = RANKX ( ALL ( vendor[vendor] ), [Spend:],, DESC )spend cumulate classify = VAR _allspend = CALCULATE ( [Spend:], ALL ( vendor[vendor] ) ) VAR _cumulatespend = CALCULATE ( [Spend:], WINDOW ( 1, ABS, 0, REL, ADDCOLUMNS ( ALL ( vendor[vendor] ), "@spend", [Spend:] ), ORDERBY ( [@spend], DESC ) ) ) VAR _percentage = DIVIDE ( _cumulatespend, _allspend ) RETURN IF ( HASONEVALUE ( vendor[vendor] ), IF ( _percentage <= 0.8, "top80%", "bottom20%" ) )this appears to be working. the ranking is working. when adding in the classify measure it runs out of memory and fails. i have a data set of millions of rows. is there a way to move this into a sub table or to solve the memory issue? technically i only need to classify it for invoices for the present year and can exclude all vendors with $0 spend.
