Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
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 Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |