3 Replies Latest reply on Mar 30, 2016 12:05 PM by Kent Sloan

    Filtering measures by dimensions, column dependent

    Stephen Russell

      I have a worksheet where I am looking at inventory level for a particular facility. Location, Item Number, On Hand Quantity as the columns.  It is filtered by the location to only show on hand quantities for location 1.

       

      Suppose I wanted to include another measure that would put the on hand quantity for Location 2 (the source location) on this same worksheet. The sheet would show the on hand quantity filtered by location 1 in a column, and then on hand filtered by location 2 in a column. The first filter to include only location 1 seems to exclude that possibility. How can I accomplish this?

        • 1. Re: Filtering measures by dimensions, column dependent
          Kent Sloan

          Hi Stephen,

           

          Instead of filtering the entire sheet what you would want to do is set up a calculation for both location 1 and 2 to compare in the sheet side by side. Below is an example of what this would look like for Location 1.

           

          Location 1 On Hand Quantity:  IF [Location]="Location 1" THEN [On Hand Quantity] END

           

          Thanks,

          Kent

          1 of 1 people found this helpful
          • 2. Re: Filtering measures by dimensions, column dependent
            Stephen Russell

            That works if I am showing all items at all locations. But I would then need a method to begin limiting rows based on the location, keeping the on hand quantity fields locked in. And if I do that any way I know, it alters the data that appears in the On hand quantity fields, if the "Location 1" is no longer in the set.

            • 3. Re: Filtering measures by dimensions, column dependent
              Kent Sloan

              Hi Stephen,

               

              You can add on to the calculation for Location 1 to include various selections. Depending on your data source you could create parameters to use as inputs to make adjustments to what rows are included in the Location 1 calculation. Another alternative that would likely be slower and more cumbersome would be to blend the same data source against itself so you could filter Location 1 with a series of quick filters then use a blend to pull in the Location 2 values. I would see this as a final option after trying anything you can with calculations and parameters.

               

              Thanks,

              Kent