Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Combine values with measure

Hi there,

Lets say I have these values:

 

MoneyState

500

CA
200FL
100CO
250AL
200NY

 

I want to combine the values of 'CA', 'FL' and 'CO'. So that will be 500+200+100 = 800. I want to give this a new column name, for example 'best-states'. A measure needs to be able to do this for me, but I don't know how. The other values of the states needs to stay the same.

 

Thanks!

Need Help
  • lkalawski's avatar
    lkalawski
    6 years ago

    Hi, 

     

    You can create new column:

    Group State = SWITCH('Table'[State],
"CA", "best-state",
"FL", "best-state",
"CO", "best-state",
'Table'[State])

    And use it to create table or visuals:

     



    _______________
    If I helped, please accept the solution and give kudos! 😀

5 Replies

  • lkalawski's avatar
    lkalawski
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    Hi, 

     

    You can create new column:

    Group State = SWITCH('Table'[State],
"CA", "best-state",
"FL", "best-state",
"CO", "best-state",
'Table'[State])

    And use it to create table or visuals:

     



    _______________
    If I helped, please accept the solution and give kudos! 😀

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      lkalawski 

      If I try this my other states are gone, only those 3 are there

       

      Edit: my Power BI wasn't showing it well, it works !

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Hi Anonymous 

    how do you define values of 'CA', 'FL' and 'CO'? If it always will be a constant try a measure

    Measure = 
IF(SELECTEDVALUE(Table[State]) IN {"CA", "FL", "CO"}, 
CALCULATE(SUM(Table[Money]), Table[State] IN {"CA", "FL", "CO"}),
SELECTEDVALUE(Table[Money])
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      az38 Yes they are constant. This seems helpful, but I have more states that I want to combine and give a new name. How can I do that? And where in your measure is the new column name?

      Thanks!

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous 

    Better create a new column

    New column =

    Switch(True(),

    [State] in {"CA","FL",,"CO"},"best-states",

    //Other connditions

    "Oher states"

    }