topic Re: Grouping using the same value twice in Developer

Grouping using the same value twice

ofeliajesus — Fri, 26 Aug 2022 18:37:31 GMT

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

Re: Grouping using the same value twice

Anonymous — Mon, 29 Aug 2022 06:11:21 GMT

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.

Re: Grouping using the same value twice

ofeliajesus — Mon, 29 Aug 2022 08:19:03 GMT

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

Re: Grouping using the same value twice

Anonymous — Tue, 30 Aug 2022 02:45:25 GMT

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.

Re: Grouping using the same value twice

ofeliajesus — Mon, 05 Sep 2022 10:32:53 GMT

Thank you Rico,

Your solution works ...

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

UNION(

SELECTCOLUMNS(),

SELECTCOLUMNS())