Forum Discussion

ofeliajesus's avatar
ofeliajesus
Helper I
4 years ago
Solved

Grouping using the same value twice

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 

 

Show and Tell
  • ofeliajesus's avatar
    ofeliajesus
    3 years ago

    Thank you Rico,

     

    Your solution works ... 

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

    UNION(

    SELECTCOLUMNS(), 
    SELECTCOLUMNS())
     
     

     

     

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.

  • ofeliajesus's avatar
    ofeliajesus
    Helper I
    3 years ago

    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_IDRegion Amount
    29042021California$200.234
    30042021North Central$650.235
    01052021West$1,140.890
    02052021Northeast$2,100.137
    03052021California$954.543
    04052021California$654.345
    05052021North Central$321.654
    06052021South Central$234.876
    06052021South East$1,140.890
    08052021North Central$2,100.137
    08052021South Central$954.543
    10052021California$654.345
    11052021West$2,100.137
    26042022Southwest$200.234
    27042022California$650.235
    28042022North Central$1,140.890
    29042022South Central$2,100.137
    30042022North Central$954.543
    01052022North Central$654.345
    01052022South Central$321.654
    01052022California$234.876
    04052022West$1,140.890
    05052022West$2,100.137
    06052022West$200.234
    07052022California$650.235
    08052022California$1,140.890
    09052022North Central$2,100.137
    10052022South Central$954.543
    11052022North Central$654.345
    12052022California$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's avatar
      Anonymous
      Not applicable
      3 years ago

      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's avatar
        ofeliajesus
        Helper I
        3 years ago

        Thank you Rico,

         

        Your solution works ... 

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

        UNION(

        SELECTCOLUMNS(), 
        SELECTCOLUMNS())