Need help with dynamic ranking using dax

[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?

NOTE: I have millions of rows of invoices. 

[Jihwan_Kim · Accepted solution]

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%" )
    )

[scootzinc]

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.