4 Replies Latest reply on Dec 31, 2018 5:17 PM by Madeleine Mindling

    Average Segment Percentages Across Order Weeks

    Madeleine Mindling

      Hi there,

       

      I've set up this Workbook to help me with an analogous issue in my real dataset :-)

       

      Using Superstore data, I need help averaging the percentages by Order Week for each segment (Consumer, Corporate, Home Office). I'm trying to set the following benchmarks:

       

      • Average % (based on weekly percentages in Sheet 1) of Home Office orders

      • Average % (based on weekly percentages in Sheet 1) of Consumer orders

      • Average % (based on weekly percentages in Sheet 1) of Corporate items

       

      Thanks in advance for your help!

        • 1. Re: Average Segment Percentages Across Order Weeks
          Simon Runc

          hi Madeleine,

           

          So if I understand correctly you want to create the Average over all the weeks and return as a value to each segment? Let me know if I've mis-understood (...wouldn't be the first time!)

           

          One way is to use an LoD

           

          [Weekly Average Percent Segment]

          {FIXED [Segment]:

           

           

          AVG(

          //LoD to Get the Share of Segment Count for Each Order Week

          {FIXED [Segment], [Order Week ]:

          COUNT([Segment])}

          /

          {FIXED [Order Week ]:

          COUNT([Segment])})

          }

           

          So the inner part get's what you have in your Viz, and then the outer part takes the average over all the weeks for each segment.

           

          In the 'No Week in Viz' tab I've removed Order Week so you can see that it works without week in the Viz. One thing to note is that the SUM of the Averages add up to more than 100%. This is due to certain segments not buying in certain weeks. The value for that segment/week is a null (in fact it's nothing as there is no record for those segment/weeks), and so doesn't get a zero, but a nothing, in those weeks. This could just be due to the superstore example and in your real world situation it wouldn't be an issue. If it is, let me know and we can work out the average by dividing the sales by a count of weeks and so get to a 100% total.

           

          Hope that helps and makes sense.

          1 of 1 people found this helpful
          • 2. Re: Average Segment Percentages Across Order Weeks
            Madeleine Mindling

            Hi Simon,

             

            Thanks so much for this. This is incredibly helpful. As you pointed out, I do have segments in my own dataset that do not appear in some of the order weeks (in my case, they're actually user registration weeks). The percentage total adds up to 102%. How do you propose I modify with non-existent values?

            • 3. Re: Average Segment Percentages Across Order Weeks
              Simon Runc

              hi Madeleine,

               

              So there are a few ways we could go here. I've given 2 below;

               

              Option 1 is just to take the overall %age over all weeks (this is like removing Week from the Level of Detail and just looking at the percents over all time)

              [Weekly Average Percent Segment - option 2]

              ({FIXED [Segment]:

              COUNT([Segment])}

              /

              {FIXED:

              COUNT([Segment])})

               

              another option would be to ignore the weeks where there isn't full coverage.

               

              I first created a calculation to pick up those weeks

              [All Segments in a Week Flag]

              {FIXED [Order Week ]: COUNTD([Segment])}

              =

              {FIXED: COUNTD([Segment])}

               

              and then use this within the original formula (nesting it as an IIF) so it only uses the weeks with all segments

              [Weekly Average Percent Segment - option 3]

              {FIXED [Segment]:

              AVG(

              //LoD to Get the Share of Segment Count for Each Order Week

              {FIXED [Segment], [Order Week ]:

              COUNT(IIF([All Segments in a Week Flag],[Segment],null))}

              /

              {FIXED [Order Week ]:

              COUNT(IIF([All Segments in a Week Flag],[Segment],null))})

              }

               

              It all depends on the final usecase for this benchmark which is best. I've added all 3 to the attached so you can see the differences

              1 of 1 people found this helpful
              • 4. Re: Average Segment Percentages Across Order Weeks
                Madeleine Mindling

                Hi Simon, apologies for the late response. I was out of town for the holiday. I think Option 3 is the best way to go for my use case. This was immensely helpful. Thank you!