Set analysis sum of others question

[DavidFoster1]

Hi all.

I have a data set with customer sales by branch of a retail company.

The report requirement is provide the following

Branch	Customer	Local Sales	Other Branches
A	John Smith	£1000	£2000
B	John Smith	£1500	£1500
C	John Smith	£500	£2500

I then need to be able to filter down to a single Branch record and the values remain the same.

The problem is how to achieve the 'Other Branches' total. My attempts at using set analysis to use the Excluded branches caused the Branch dimension values to appear even though they were filtered.

Any help appreciated.

[joshabbott]

The set analysis should be centered around the {1} I would think.  Could you let us know your expression for the 'Other Branches' column you are having problems with?

[MK_QSL]

Create a Straight Table

Dimension

Branch

Customer

Expression

SUM([Local Sales])

SUM(ALL [Local Sales])  - SUM([Local Sales])

[VishalWaghole]

Hi David,

You can also use,

=Sum(TOTAL LocalSales) - sum(LocalSales)

-- Regards,

Vishal Waghole

[DavidFoster1]

SUM({$<Branch=>} Sales) - SUM(Sales)

[DavidFoster1]

Sorry but neither of the ALL or TOTAL suggestions work because there will be multiple customers in the list (should have made that more explicit)

[MK_QSL]

Can you please post your sample test data in excel file?

[#8]

Hello David,

Can you post a sample app?

Did you try using P() and E() in set analysis. That might help in this case.

Thanks,

Singh

[simenkg]

sum({1}Total Sales) - sum(Sales)

[DavidFoster1]

Sample Data

BRANCH	CUSTOMER	SALES
A	JOHN SMITH	1000
A	JOE BLOGGS	1500
A	JANE DOE	500
B	JOHN SMITH	2000
B	JOE BLOGGS	2500
B	JANE DOE	500
C	JOHN SMITH	3000
C	JOE BLOGGS	0
C	JANE DOE	1000

and the result I am looking for that can be filtered down to a single branch and only show 3 rows.

BRANCH	CUSTOMER	LOCAL SALES	OTHER BRANCHES
A	JOHN SMITH	1000	5000
A	JOE BLOGGS	1500	2500
A	JANE DOE	500	1500
B	JOHN SMITH	2000	4000
B	JOE BLOGGS	2500	1500
B	JANE DOE	500	1500
C	JOHN SMITH	3000	3000
C	JOE BLOGGS	0	4000
C	JANE DOE	1000	1000