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-cgao-msft]

Hi @scootzinc ,

Please try these measures.

the net spend = CALCULATE(SUM('Table'[spend]),'Table'[year]=2023)

Ranking = 
IF (
    [the net spend] && HASONEVALUE ( 'Table'[vendor] ),
    RANKX (
        ALLSELECTED ( 'Table'[vendor], 'Table'[group] ),
        [the net spend],
        ,
        DESC,
        SKIP
    )
)

2023 vendor spend = CALCULATE(SUM('Table'[spend]),'Table'[year]=2023,ALLEXCEPT('Table','Table'[vendor]))

Group by 2023 vendor spend = 
IF(
    HASONEVALUE('Table'[vendor]),
    VAR _spend_by_vendor = 
    ADDCOLUMNS(
        ALLSELECTED('Table'[vendor]),
        "spend",[2023 vendor spend]
    )
    VAR _all_spend = 
    CALCULATE(
        [2023 vendor spend],
        ALLSELECTED('Table')
    )
    VAR _cur_spend_amount = [2023 vendor spend]
    VAR _cumulated_spend = 
        FILTER(
            _spend_by_vendor,
            [spend] >= _cur_spend_amount
        )
    VAR _cumulated_spend_amount = 
        SUMX(
            _cumulated_spend,
            [spend]
        )
    VAR _cur_cumulated_pct = 
        DIVIDE(
            _cumulated_spend_amount,
            _all_spend
        )
    VAR _result = 
        SWITCH(
            TRUE(),
            ISBLANK(_cur_cumulated_pct),BLANK(),
            _cur_cumulated_pct<=0.8,"top 80% of spend",
            "bottom 20% of spend"
        )
    RETURN
        _result
)

result.

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum