How to refer between dimensions (customer vs. store ID)

[#1]

Hi,

I have sales data where is same ID at customer and store dimension (Internal/external sales) and I´m trying to achieve a object where I can see total sales, i.e first column internal sales (#Store --> #Customer) and second column sales #Store -->

In other words, warehouse is selling goods to its customer (customer ID 100) and this customer (store ID 100) is selling those goods to consumers. I´m trying to add both of those sales in one row.

Previously I have done this like

if(WildMatch(#Customer,'100*'), sum({<#Store={'100'}>}Total Sales))

but now I have about 70 rows so I don´t want to do it that way.

I tried something like that but with no succees:

sum({<#Store={"=$(=Only({<Chain={'aaa'}>}#Customer))"}>}TOTAL Sales)

Only({<Chain={'aaa'}>}#Customer) <-- only this returns right customer number for each row

There is #Customer as dimension at my object.

Is there some way I can achieve what I´m wanting?

Thanks in advance!

[sunny_talwar]

Not entirely sure I understood what you are trying to do. Would you be able to share a sample with your expected output?

[#3]

CustomerID	Warehouse sales to its customer (=CustomerID)	Store sales to its customers (StoreID = CustomerID)
100	1000	2500
150	1200	2400
200	1300	2700

At data there are separate rows for warehouse sales to its customer and store sales to consumers.

I´m not sure how to put this problem into words but hopefully you get my point So what I want to achieve is to see at the same time what warehouse is selling its customers (customer = store) and at the same time what stores are selling to consumers.

[Anonymous]

Dont know, exactly understood what you are looking for, I would suggest:

Take Calculated Dimension: If(#Customer like '100*', #Customer)

Expression: sum({<StoreID={'100'}>} Sales)

[#5]

Thanks but is there anyway to get this work dynamically? Because I have those stores about 70 so is it possible to get dimension value for each row and refer to that value in set analysis (storeID = customerID)?