Forum Discussion

donodackal's avatar
donodackal
Helper I
4 years ago
Solved

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 (Th...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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's avatar
donodackal
Helper I
4 years ago

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.