9 Replies Latest reply on Jan 13, 2017 1:02 PM by Stephen Rizzo

# HOW TO SUM ON A SPECIFIC VALUE ACROSS MULTIPLE DIMENSIONS, HELP!

I have a data set that has transactions with a Buyer, Seller and Volume of the transaction. In certain cases A may buy from B or B may sell to A or A may buy from C....etc. etc.

Is it possible to show a bar graph with the amount of volume that A has bought and sold. I can easily put it into text table matrix with total, but woudl like a simple bar chart that show total volume of A whereby they were either a Buyer or Seller. Buyer and Seller are two seperate dimensions.

See attached workbook.

Thank you,

Chris

• ###### 1. Re: HOW TO SUM ON A SPECIFIC VALUE ACROSS MULTIPLE DIMENSIONS, HELP!

Hi Chris

I am on T10.1.3 so attaching a workbook is not going to work but see the screen print below

Will this work for you?

Jim

• ###### 2. Re: HOW TO SUM ON A SPECIFIC VALUE ACROSS MULTIPLE DIMENSIONS, HELP!

No.

What I would like to see is "A" having a total volume of 4,500. E.g. A bought 1,000 from B, Sold 3,000 to C and sold 500 to B = total of 4,500.

Chris

• ###### 3. Re: HOW TO SUM ON A SPECIFIC VALUE ACROSS MULTIPLE DIMENSIONS, HELP!

Chris,

Edit data source  and pivot data.

Rename Fields

Pivot Field Values==>      Customer 2

Thanks,

Shin

1 of 1 people found this helpful
• ###### 4. Re: HOW TO SUM ON A SPECIFIC VALUE ACROSS MULTIPLE DIMENSIONS, HELP!

This would work, however, curious if there is a solution without changing the data?

The data will be connected to a database, not sure if it will be an issue to pivot the data.

1 of 1 people found this helpful
• ###### 5. Re: HOW TO SUM ON A SPECIFIC VALUE ACROSS MULTIPLE DIMENSIONS, HELP!

Chris,

This will be a challenge because you want each row of data to essentially be counted twice (each transaction affects the bars for 2 different parties).

If you're connecting to a data source that supports Custom SQL Queries, you can pivot with a custom query and a union:

`SELECT  BUYER AS Party,  'Buyer' AS Role,  VolumeFROM TableUNIONSELECT  SELLER AS Party,  'Seller' AS Role,  VolumeFROM Table;`

If you have only a very few number of parties, you can write LOD expressions for each of them:

`{ FIXED : SUM( IF [Buyer] = 'A' OR [Seller] = 'A' THEN [Volume] END ) }`

Repeat for each party, then drop them all the LOD calcs onto your view using "Measure Values".

Those are the only 2 approaches that are coming to mind, right off the bat.

• ###### 6. Re: HOW TO SUM ON A SPECIFIC VALUE ACROSS MULTIPLE DIMENSIONS, HELP!

I cannot think out the way without duplicating the data.

Not sure database allow "Union" or not neither, but here is option to use "Union".

if [Table Name]="Sheet1" then [Seller] else [Buyer] end

[Title]

if [Table Name]="Sheet1" then "Seller" else "Buyer" end

Thanks,

Shin

• ###### 7. Re: HOW TO SUM ON A SPECIFIC VALUE ACROSS MULTIPLE DIMENSIONS, HELP!

Hi Chris,

This has been done by two ways

2. LOD calculations.

Please see my attached work book for reference.

• ###### 8. Re: HOW TO SUM ON A SPECIFIC VALUE ACROSS MULTIPLE DIMENSIONS, HELP!

Thank you for response, however, this still does not provide the correct solution. I want to see total volume traded by each customer whether they bought or sold... e.g. for A, I would like to see a total volume of 4,500 shown... 1,000 Bought + 3,500 Sold.

Chris

• ###### 9. Re: HOW TO SUM ON A SPECIFIC VALUE ACROSS MULTIPLE DIMENSIONS, HELP!

The easiest solution in that case is to pivot your Buyer and Seller fields.

EDIT: Nevermind, this was already suggested earlier in the thread. My apologies. In my opinion this is the best solution.