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 ...
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())
     
     

     

     

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())