Group companies

[johnmelbourne]

I want to be able to group three subsidiaries into a new parent entity name for reporting. For example:

I have
Company       Sales
Company A   $100
Company B    $150
Company C    $200

Company D    $100
...
Company Z   $400

I want a report that shows total sales for all individual companies, but treat Company A, B and C as one 'virtual' company.

So a table that results

Company                             Sales

New Company Name           $450

Company D                           $100

...

Company Z                            $400

That is group company A, B and C revenue but not group the rest.

I was thing of a switch statement for a calculated column where

Company A, New Company Name

Company B, New Company Name

Company C, New Company Name

Company D, Company D

...

Company Z, Company Z

but wondered what advice others would have to solve my problem.

Thanks in advance.

[Anonymous · Accepted solution]

Depending on how many companies you have, you can use the group function and put them in "buckets":

Then just add them together and leave the others ungrouped like this:

Then replace the dimension on your graphs with the new Companies (groups) dimension.

[johnmelbourne]

Thank you! Especially for going to the trouble of adding the screen captures. Much appreciated and will solve my problem (only 30 companies).

Thanks again.

John