Need help with dynamic ranking using dax

scootzinc — Thu, 30 Mar 2023 19:50:30 GMT

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.

Re: Need help with dynamic ranking using dax

Jihwan_Kim — Fri, 31 Mar 2023 03:34:35 GMT

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

Re: Need help with dynamic ranking using dax

scootzinc — Fri, 31 Mar 2023 15:17:21 GMT

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.

topic Re: Need help with dynamic ranking using dax in DAX Commands and Tips

Need help with dynamic ranking using dax

Re: Need help with dynamic ranking using dax

Re: Need help with dynamic ranking using dax