Combine values with measure

[Anonymous]

Hi there,

Lets say I have these values:

Money	State
500	CA
200	FL
100	CO
250	AL
200	NY

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!

[amitchandak]

@Anonymous 

Better create a new column

New column =

Switch(True(),

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

//Other connditions

"Oher states"

}

[lkalawski · Accepted solution]

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]

@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]

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]

@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!