How add dummy value as grouping to Dimension

[#1]

Hi all, hoping someone can suggest an approach for this. As a simple example what would be the best practice to group a specific set  of dimension values and treat them as just another dimension, i.e. all Big companies grouped under single dimension record.

I also have a complication in that I need to show the Company Dimension across the top and the measures in rows (previous post for this: Sum of Columns in Pivot table)

Many thanks in advance!

[sunny_talwar]

Do you have a sample you can share?

[#3]

Sure, here you go...

[sunny_talwar]

Are you looking to see something like this?

I used a calculated dimension instead of Company -> =Left(Company, Index(Company, ' ', 2))

or are you looking to see them separately and then grouped?

[#5]

Hi, ideally I need to see them in the same chart, and the names are simply the actual names of companies so can't use Left(Company, Index(Company, ' ', 2)) but hard coding the names of the group is ok if that helps as the names defined in the group shouldn't change.

[maxgro]

T1:

Load * inline [

Company,No of Bike Sale, No of Car Sale

Big Company A,5,20

Big Company B,24,3

Small Company C,8,2

Small Company D,15,1

];

T11:

load Company as CompanyGroup, Company Resident T1;

load left(Company, index(Company, 'Company') +len('Company')) as CompanyGroup, Company Resident T1;

[#7]

Hi, thanks for this, although this relies on Company being in the name I can probably get round that by prefixing the actual company names with something however  how would I go about showing all the individual companies along with the groupings in the same chart or would I need to create two separate charts and try aligning them together? also what if I only want to show Big Company as an extra grouping?

Many Thanks!

[maxgro · Accepted solution]

you can use company group as dimension when you need the extra group (Big Company, Small Company)

or Company as dimension  for "standard" companies

[#9]

Good point, many thanks will give it a try!!!