Forum Discussion
dynamic ranking
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?
1 Reply
Hi scootzinc ,
I suggest you to try code as below to create measures.
Spend based on vendor & group, year 2023 = CALCULATE(SUM('Table'[#spend]),FILTER(ALLEXCEPT('Table','Table'[vendor],'Table'[group]),'Table'[year] = 2023))Rank = IF(MAX('Table'[year]) = 2023,RANKX(ALL('Table'),[Spend based on vendor & group, year 2023],,DESC,Dense))Flag = VAR _MAXRANK = MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[year] = 2023 ), [Rank] ) VAR _TOP80 = 0.8 * _MAXRANK RETURN IF ( MAX ( 'Table'[year] ) = 2023, IF ( [Rank] <= _TOP80, "Top 80 %", "Bottom 20%" ) )Result is as below.
Best Regards,
Rico ZhouIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
