Forum Discussion
Help creating new DAX Column
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 |
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.
5 Replies
Hi,
Share the download link of your PBI file.
The PBI file has sensitive data.
Anonymise the data in the PBI file and share the download link. Ensure your measures are already written in that file.
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.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.
