2 Replies Latest reply on Dec 6, 2016 7:42 AM by arjun.kumar.1

    Using a SUM as a dimension / global filter

    arjun.kumar.1

      Hi all

       

      I have foraged the forums for an answer but found nothing that suits my situation, so hoping someone could help me with an answer directly.

       

      Example Situation - Sample Superstore Dataset:

      • Take the SUM of Sales per Customer Name.
      • Aim to use this as a filter, to filter for customers who have Sales (sum) over $10,000.
      • I want this filter to apply to all sheets on the dashboard. (see attached, for example - I want my Location & Segment sheets to exclude the rows of data that have customers who have not spent over $10,000.

      A workaround at this point that I have been using is manually create a pivot table / new column for Total Sales. However this means I cannot:

      • Use filter for 'Order Date' to filter date to X and Y. I now want the dashboard to show all customers with over $10,000 sales total, within order dates X and Y.

       

      So far I have tried:

      1. Pivot Table source data onto new tab then link back. (Same as new column effectively)
      2. New column in source data. (same as pivot table)
      3. SUM IF
      4. Window Calcs i.e. Window_Sum

      The pivot tables and new column in the dataset work well as I can change from a measure to a dimension and the filter works well. However the date variable throws this off.

       

      I hope that makes sense! I have attached a sample workbook for reference. Appreciate any answers / suggestions!

       

      Let me know if you have any questions.

       

      Thanks in advance

       

      Arjun

        • 1. Re: Using a SUM as a dimension / global filter
          David Li

          Hi Arjun, you should be able to do this using LOD calculations. For your filter, you would use something like this:

          { FIXED [Customer Name] : SUM([Sales]) }

          When you drag this into the filters shelf, use "All Values" as the filter type and then set it to greater than 10,000.

          Right-click this new filter and set it to apply to all worksheets. Then, you should be all set!

           

          Note that if you're trying to filter other dimensions (e.g. figure out who has sales above $10k for a particular Category), you may have to set those other filters as context filters for this to work properly.

          1 of 1 people found this helpful
          • 2. Re: Using a SUM as a dimension / global filter
            arjun.kumar.1

            Perfect! Thank you so much!

             

            So far it looks like using { FIXED [Customer Name] , [Location] : SUM([Sales]) } works for filtering other dimensions too.

             

            Really appreciate your help!