Forum Discussion

Yuiitsu's avatar
Yuiitsu
Helper V
6 years ago
Solved

Creating new column with custom grouping

Hi Experts

 

I would like to create a new column to group my customer base on certain rules. Please take a look at the example I created below

 

CustomerItem Purchased
AAAGT
BBBGT
AAAAT
CCCAT
CCCFT
DDDGT
DDDGT
AAAHT
CCCHT

 

I want the rule to be like this:

New column= if customer = AAA = "AAA",

if customer = BBB = "BBB"

anything else will be grouped as "others"

 

Can anyone tell me how to set this formula? 

Let me know if my request is confusing. 

Thanks!

Need Help
Show and Tell
Tutorial Requests
  • v-alq-msft's avatar
    v-alq-msft
    6 years ago

    Hi, Yuiitsu 

     

    Based on your description, you may create a meausre or a column as follows.

     

     

    Result = 
var _customer = SELECTEDVALUE('Table'[Customer])
return
IF(
    ISFILTERED('Table'[Customer]),
    IF(
        _customer in {"AAA","BBB"},
        _customer,
        "others"
    )
)

Result Column = 
IF(
    'Table'[Customer] in {"AAA","BBB"},
    'Table'[Customer],
    "others"
)

     

     

     

    Result:

     

    Best Regards

    Allan

     

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

3 Replies

  • v-alq-msft's avatar
    v-alq-msft
    Community Support
    6 years ago

    Hi, Yuiitsu 

     

    Based on your description, you may create a meausre or a column as follows.

     

     

    Result = 
var _customer = SELECTEDVALUE('Table'[Customer])
return
IF(
    ISFILTERED('Table'[Customer]),
    IF(
        _customer in {"AAA","BBB"},
        _customer,
        "others"
    )
)

Result Column = 
IF(
    'Table'[Customer] in {"AAA","BBB"},
    'Table'[Customer],
    "others"
)

     

     

     

    Result:

     

    Best Regards

    Allan

     

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

    • Yuiitsu's avatar
      Yuiitsu
      Helper V
      6 years ago

      Hi Experts

       

      I found out that I could just right click the column and add new group.

       

      It is so much easier!

       

      Thanks alot for all the info guys!