Need to remove sales from a lower level when looking at sales at a higher level

This topic has 0 replies, 1 voice, and was last updated 6 years, 1 month ago by Frank.

  • Author
    • #298
      • Topics Started - 80
      • Replies Written - 53

      The idea of eliminating sales for specific product, customer or other lower level of information when looking at information at a higher level like division, company, region etc. came up while talking with someone the other day. We were able to eliminate the sales for a specific product from the upper level sales pretty easily.

      The example business case:
      I’ve got a specific product that we are selling that we are going to remove from our mix.
      The Stratum case:
      This product is one of thousands that we have and it does not have any specific coding that allows us to use another dimension (like Product Category or Brand) to eliminate it by simply filtering on dimensions.

      In my example the product we are trying eliminate from our sales is Delicious Red Apples – 954009.
      I’ve already got a measure that is YTD sales and I’m looking at it by Division. To get the Apple sales out of the division number I want to subtract the sales of Apples out of each of the division sales (or what ever other level I’m looking at).

      Step 1 – Create a Tuple for product 954009 – Delicious Red Apples so we have the sales specific to this product, regardless of what level of data we are looking at.
      This calculated measure looks like:

      ([Product].[Product].[Product].[954009],[Measures].[Data1 (Actual Sales Sales Amount Jan TY to Aug TY)])

      To create this I created a calculated measure, selected Hierarchies (Product in this case) and then selected Members under Product – and then selected 954009 from the member list. Follow this up with a “.” then insert the measure you want to see the sales for case – in my case it was Actual Sales. Enclose the expression in ( ) and you have the tuple!

      Next – And don’t click OK to Accept changes just yet!
      We want to add an axis filter so that we only get rows that have data. If you don’t do this, the calc can run away from you and try to calculate for all the possible rows of the dimension you are looking at. For example if you were looking at Ship to’s down the side of the view – once you add the tuple described above it will try to calculate for all ship to’s even if there are no sales, so if you have 100K ship to’s you’re gonna get 100,000 rows – not good! So…add this axis filter….what you ask is an axis filter?? Right Click on the little down arrow in the upper left corner of the grid – and Edit the filter – this allows you to apply a filter the axis to any data that is displayed in the rows. The filter we want in my example is:

      [Measures].[Data1 (Actual Sales Sales Amount Jan TY to Aug TY)]<>0

      This will only show rows that have Actual Sales – note this is the same measure we built the tuple around.

      Now click ok then OK to accept your changes and you should be good to go. Now you can subtract the sales for Delicious Red Apples from your sales number and see what the impact of removing those sales is.

      BTW you can drill down to any dimension except the dimension you created the tuple around and this technique will work. If you want to get to the product (in my example this is the level we built the tuple around) and not see the product Delicious Red Apples there is a post on eliminating certain dimension members – take a look at that.

      Questions comments? Post ’em here and I’ll respond….after the holiday!

Viewing 0 reply threads

You must be logged in to reply to this topic.


We're not around right now. But you can send us an email and we'll get back to you, asap.


©2022 KLEO Template a premium and multipurpose theme from Seventh Queen

Log in with your credentials


Forgot your details?