Creating groups question

[Anonymous]

Hi

I have a question and that is i cannot create groups since the normal way in power BI because i do not have access to update the tables. Is there a way using Dax or etc where I can group the table below all the yellow items in group1 and the blue in group2. Thanks

[Cmcmahan]

Is there some thing that links the groups to each other, or is it arbitrary just based on this ID?

If it's arbitrary, you can create a claculated column like this:

GroupBasedOnID = 
    SWITCH( 'Table1'[ORG_ID], 
        "ARDC", "Group1",
        "BCGL", "Group1",
        "BRP", "Group1",
        "CBN", "Group1",
        "CXMC", "Group2",
        "EDC", "Group2",
        "ER1", "Group2",
        "ER2", "Group2",
        "IPDC", "Group2",
         BLANK() )

Otherwise you can use other methods to split these groups if there's a better way to determine which group a row should be in.

[Anonymous]

HI Cmcmahan

I am trying to link the groups by a toal sum field YTD-PPM to de displayed like below.

 Group 1 =  1,845 (which would be the total sum of all the items in Group 1)

 Group 2 = 3,045 ((which would be the total sum of all the items in Group 2)

[Cmcmahan]

What you do with group aggregations after the values have been grouped isn't relevant.  I'm asking about the original groupings before you do any totals/averages/etc.

If you handed me a printed out copy of your data tables, and told me to highlight all the Group1 rows, how would I know they're Group1?  Just by using the key you provided?  Or is there another portion of the data that indicates which group?

I ask mostly because in the future, if you add a new group, you'll have to remember to come back and edit this calculated column specifically, but if there was a way to determine without a lookup table, you could do that instead and never have to mess with the groups again.

[Anonymous]

Hi

I do not have any lookup tables or any tables that tells that that the items in yellow are group1 and the items in blue belong to group 2. It arbritary. I only know that they are group 1 and group 2 base off the ORG_NAME. Let me know if this makes it clear. Trying to create 2 groups with the sum of the YTD-PPM field.

[Cmcmahan · Accepted solution]

So yeah, adding a calculated column with the code from before (or some variant thereof) is going to be the way to accomplish this then.

GroupBasedOnID = 
    SWITCH( 'Table1'[ORG_ID], 
        "ARDC", "Group1",
        "BCGL", "Group1",
        "BRP", "Group1",
        "CBN", "Group1",
        "CXMC", "Group2",
        "EDC", "Group2",
        "ER1", "Group2",
        "ER2", "Group2",
        "IPDC", "Group2",
         BLANK() )

[dax]

Hi bu965,

I think you could refer to Cmcmahan’s suggestion to create calculated column to group. In addition, you said you want to link this based on total sum. Could you please explain for me : If you don’t group, how to you know it will get total of 1845? So if possible, could you please inform me more detailed information(such as your sample date )? Then I will help you more correctly.

Best Regards,

Zoe Zhi

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