Grouping using the same value twice

[ofeliajesus]

Hello,

I have the below regions 

Basically, I want to have all the below plus Non-California (with all except California) in the same field is it possible with Groups 

can I use the same field twice? Is it better to use DAX ?

How can I do this?

Example 

California 

North Central

Northeast

South Central

South East

Southwest

West

Non-California

Any help would be appreciated.

Thank you 

[ofeliajesus]

Thanks, Rico,

This is not what I want, my column Region doesn't have Non-California.

I have a fact table like the one below one

Date_ID	Region	Amount
29042021	California	$200.234
30042021	North Central	$650.235
01052021	West	$1,140.890
02052021	Northeast	$2,100.137
03052021	California	$954.543
04052021	California	$654.345
05052021	North Central	$321.654
06052021	South Central	$234.876
06052021	South East	$1,140.890
08052021	North Central	$2,100.137
08052021	South Central	$954.543
10052021	California	$654.345
11052021	West	$2,100.137
26042022	Southwest	$200.234
27042022	California	$650.235
28042022	North Central	$1,140.890
29042022	South Central	$2,100.137
30042022	North Central	$954.543
01052022	North Central	$654.345
01052022	South Central	$321.654
01052022	California	$234.876
04052022	West	$1,140.890
05052022	West	$2,100.137
06052022	West	$200.234
07052022	California	$650.235
08052022	California	$1,140.890
09052022	North Central	$2,100.137
10052022	South Central	$954.543
11052022	North Central	$654.345
12052022	California	$321.654

and I want is create a new group that gives me all the regions more Non-California because I want a Matrix-like

[Anonymous]

Hi @ofeliajesus ,

I think you can try to create a calculated table to achieve your goal.

Table 2 =
VAR _Non_California =
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[Region ] <> "California" ),
        'Table'[Date_ID],
        "Region", "Non-California",
        "Amount", CALCULATE ( SUM ( 'Table'[Amount] ) )
    )
RETURN
    UNION ( 'Table', _Non_California )

Result is as below.

Best Regards,
Rico Zhou

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

[ofeliajesus · Accepted solution]

Thank you Rico,

Your solution works ... 

In the Union, I needed to change it to have the same fields same order ...

UNION(

SELECTCOLUMNS(), 

SELECTCOLUMNS())

[Anonymous]

Hi @ofeliajesus ,

I suggest you to create a calculated column by dax.

Group = 
IF('Table'[Region] = "California",'Table'[Region],"Non-California")

Result is as below.

Best Regards,
Rico Zhou

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