Eliminating the selection of one dimension.

[maahivee]

Hello Guys,  I have same issue with a slightly different request.

I now have 2 dims order number and item number, but the point is i want to show the Sum(sales) for each item number but regardless the order number

To elaborate- I want to see the sum(sales) whenever i select an order number all the lines for that order come up but they show only the data for that order.

But what i want to do is show the Sum(sales) for all the items in that order regardless of the order number, Whatever is the entire sales for that item not only for that particular order. 

But the users want to still filter it based on the order number and look what are the entire sales for all the items in that order. How can i achieve this.

Sum({$<[Order Number]=>}  [Sales])

Sum(Total [Sales])

Sum(All<[Item Number]>[Sales])

I have used all these expressions but nothing is working.

Thanks in advance.

[ziadm]

Your Syntax seems to be fine ..

Sum({$<[Order Number]=>}  [Sales]) 

This should work as this gives you sum of Sales of the selected Item Numbers regardless of the order number.

I think you have to revisit you data model and make sure that your Fact table and dimension table are linked correctly.

You may post a picture of you data model

[maahivee]

Thank You Ziad for the reply, Yes actually that above syntax is working.

But the problem with it is that the user wants to select a particular order and if that order has 10 items it should show only those 10 rows. But since we are eliminating the selection of order Number from that expression it is showing all the lines for all the orders. Also it is not summing the sales only by item Number. But by both order and item number.

Sum({$<[Order Number]=>}  [Sales]) -- is just letting us not to select the order number but not meeting the actual requirement which is showing the sum of total sales for each item in that order.

say we have 20 orders which have the item 123, in that case all the 20 rows for that item should show 500$ if 500$ is the total sales since the beginning of the item creation.

[#4]

Hi Maahivee,

Try this - sum({1<[Item Number]=P([Item Number])>}Sales)

Cheers

[ramoncova06 · Accepted solution]

this should work

Sum({$<[Order Number]=,[Item Number] =p({$})>}  [Sales])

[maahivee]

Thanks Ramon for the answer, Can you please explain the expression in a brief.

[ramoncova06]

with [Order Number]= you are telling it to ignore the sale order selection and [Item Number] =p({$}) is bypassing the exclusion and bring in only the item numbers that belong to your current selection