Help creating new DAX Column

[donodackal]

Hi there

The below table is a summary of multiple invoices summed up against the supplier. I would like to create a new column that broadly categories each supplier based on their Total Spend (The Total Spend is another measure I included that sum up all invoices for a supplier). So, for every supplier and based on their Total Spend, the new column should tag them as:

Suppliers with a Total Spend of over $1,000,000 will have "million-dollar club" in the new column; 

Suppliers with a Total Spend between $200,000 to $1,000,000 will have "200K to 1M club" in the new column;

Suppliers with a Total Spend between $25k to $200k will have "25k to 200k club" in the new column; or

Suppliers with a Total Spend below $25k will be called "Penny value club"

Can I get help creating the correct DAX to add this column? Appreciate all your help.

Below is an example of the table:

Supplier Name	Count of Invoice Number	Average of Invoice Amount	Total Spend
Supplier 1	1315	$                                1,756.87	$ 2,310,278.59
Supplier 2	629	$                                2,768.35	$ 1,744,059.58
Supplier 3	614	$                                2,011.89	$ 1,235,298.43
Supplier 4	157	$                                6,152.17	$    965,890.41
Supplier 5	23	$                              35,729.16	$    821,770.74
Supplier 6	288	$                                2,564.16	$    738,477.08
Supplier 7	49	$                              13,240.31	$    648,775.00
Supplier 8	224	$                                2,617.42	$    586,301.55
Supplier 9	30	$                              19,007.90	$    570,236.90
Supplier 10	365	$                                1,484.28	$    541,762.80
Supplier 11	49	$                              10,586.39	$    518,732.95
Supplier 12	36	$                              13,916.86	$    501,007.00
Supplier 13	67	$                                6,646.60	$    445,322.50
Supplier 14	73	$                                5,371.14	$    392,093.50
Supplier 15	104	$                                3,631.42	$    377,667.95
Supplier 16	24	$                              15,688.75	$    376,530.00
Supplier 17	567	$                                    618.96	$    350,951.52
Supplier 18	2	$                            174,240.00	$    348,480.00
Supplier 19	16	$                              21,059.38	$    336,950.00
Supplier 20	32	$                              10,105.95	$    323,390.36
Supplier 21	49	$                                6,391.70	$    313,193.50
Supplier 22	189	$                                1,600.50	$    302,494.94
Supplier 23	374	$                                    761.91	$    284,953.25
Supplier 24	86	$                                3,292.18	$    283,127.11
Supplier 25	42	$                                5,772.45	$    242,442.75
Supplier 26	471	$                                    481.15	$    226,623.37
Supplier 27	214	$                                1,036.00	$    221,705.00
Supplier 28	410	$                                    531.77	$    218,027.00
Supplier 29	122	$                                1,754.88	$    214,094.92
Supplier 30	32	$                                6,322.59	$    202,323.00
Supplier 31	30	$                                6,718.37	$    201,551.00
Supplier 32	174	$                                1,138.17	$    198,041.80
Supplier 33	343	$                                    564.08	$    193,480.00
Supplier 34	36	$                                5,326.86	$    191,766.81

[Anonymous · Accepted solution]

Hi @donodackal ,

Please try:

Categories = 
var _sum=CALCULATE(SUM('Table'[ Total Spend ]),ALLEXCEPT('Table','Table'[Supplier Name]))
return SWITCH(TRUE(), _sum>1000000 ,"million-dollar club",_sum>=200000 ,"200K to 1M club",_sum>=25000 ,"25k to 200k club","Penny value club")

Output:

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[donodackal]

Hi Evelyn, thank you for your help. When I try this formula, I get all suppliers tagged under the million-dollar club. Btw, my table has more than one row for all suppliers as all of them have many invoices, and the total spend is just the sum of all invoices. 

[Ashish_Mathur]

Hi,

Share the download link of your PBI file.

[donodackal]

The PBI file has sensitive data.

[Ashish_Mathur]

Anonymise the data in the PBI file and share the download link.  Ensure your measures are already written in that file.