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.
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
I attached in the question above.
1 of 1 people found this helpful
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.