dynamic ranking

[scootzinc]

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?

[v-rzhou-msft]

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.