2 Replies Latest reply on Feb 13, 2019 9:23 AM by Michael Markman

    Fixed LOD when applying filters

    Michael Markman

      Hi All - I've attached a sample workbook from the superstore where I've created 3 calculations to do the following:

      1) Calculate the average days from order to shipment

      2) Calculate a weighted average from order to shipment based on sales value

      3) Calculate the weighted average contribution to the TOTAL average weighting of the current sort. So this column should add to the total average in #2. Also, although this is a formula I created, it is saying it is a table calc - not sure why and if that is what is causing me trouble.

      #4) Same as #3, but keep it a fixed number that doesn't change when I sort

       

      ** #4 is where I need help, because I'm trying to make it a fixed calculation that will always show the contribution to the total weighting regardless of my filter. I keep running into aggregation issues when trying to develop a better formula, so I'm probably missing something simple. In my example, Alabama has the following with no state filter applied:

      when I filter on just Alabama and Arizona i get the following:

       

      I'm looking for a 4th column that would show Alabama as 0.030 and Arizona as 0.063.

       

      Your help is greatly appreciated.

       

      thanks,

       

       

      Mike

        • 1. Re: Fixed LOD when applying filters
          Jim Dehner

          Hi see the attached

          first Total() is a table calculation so when you used to to calculate a % it becomes aa  table calc

          returns this

           

          Jim

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Fixed LOD when applying filters
            Michael Markman

            Hey Jim - I appreciate the quick response and this got me to the correct answer with the final output being:

             

            Only issues I saw with your solution:

            1) "Avg. Weighted Time to Ship" has a total when you unfiltered of 199.8. I think the measure needs to be changed to average, but even that doesn't get the correct answer because it is coming up with 4.077 when state is fixed, where i think the true weighted average across all sales is 3.923.

            2) The "Weighted Credit Terms as Part of Total" column should equal the 2nd column if done correctly as this is just showing each states contribution to the overall weighting.

             

            this is what the unfiltered version I was looking for is:

             

            Thanks again,

            Mike

            2 of 2 people found this helpful