10 Replies Latest reply on Aug 10, 2016 11:58 AM by Tom W

# Grouping Members in String and Creating Subsets

Hi Folks, Have a question for you:

We have values like:

A1

A3

B6

B3

C1

C7

D3

E0

F2

G4

How can we create Groups like:

A (includes all As)

B (includes all Bs)

C (includes all Cs)

D (includes all Ds)

E (includes all Es)

F (includes all Ds) …

Subset 1 (includes only A, B and C)

Subset 2 (includes only A and E)

...

Thanks!

• ###### 1. Re: Grouping Members in String and Creating Subsets

Create a calculated field (lets call this Group Code) to figure out the group as LEFT([Value,1). That will give you A through F and you can filter on that.

Then if you want to filter on a subset you could create a calculated field like [Group Code] = "A" or [Group Code] = "B" or [Group Code] = "C"

This will result in a boolean (Yes/No) field which  you can drag into the filters and set as true or false.

Repeat for Subset 2.

• ###### 2. Re: Grouping Members in String and Creating Subsets

Hi Aleksander,

Please find my approach below and let me know if you have any question. You can easily create a bucket by using "Startswith" function.

You can further create a subset by following the below approach

• ###### 3. Re: Grouping Members in String and Creating Subsets

Thanks Rajeev Pandey, but how am I gonna make the:

Subset 1 (includes only A, B and C)

Subset 2 (includes only A and E)

Thanks!

P.S. I attached the workbook...

• ###### 4. Re: Grouping Members in String and Creating Subsets

1 of 1 people found this helpful
• ###### 5. Re: Grouping Members in String and Creating Subsets

I was asking for:

I'm trying to get this one - All companies, plus the "Subset 1" and "Subset 2" in the same view:

• ###### 6. Re: Grouping Members in String and Creating Subsets

Hi Alex,

I am not able to understand what you are actually asking here,

Create a hierarchy by dragging two above fields on top of another.Can you please post the worksheet

• ###### 7. Re: Grouping Members in String and Creating Subsets

I attached in the question above.

Thanks Rajeev!

• ###### 8. Re: Grouping Members in String and Creating Subsets

Hey Tom, I was trying to do Subsets within same group. Something like this:

I attached the workbook in the question above.

Thanks!

• ###### 9. Re: Grouping Members in String and Creating Subsets

You would need to create calculated fields for each company.

I.e. Calc_a = SUM(IF LEFT(Value,1) = "A" THEN Value END)

You would need one of those for A through G.

Then you would need a calculated field for subset a

Subset_a = SUM(IF LEFT(Value,1) = "A" or LEFT(Value,1) = "B" or LEFT(Value,1) = "C" then Value END)

Repeat the same for subset B.

Then in your report, drop Measure Names onto the rows and measure values onto the label button. Select all the fields you need and you have your answer.

I suspect you're after something which is more dynamic than this though and adds the 'subsets' as rows below company G. Unfortunately this isn't possible straight up. You could potentially create a second sheet then use a dashboard to layer the two objects together.

1 of 1 people found this helpful