5 Replies Latest reply on Nov 7, 2018 2:14 PM by Michel Caissie

    LOD and Max Calculations to Show Potential

    Natalie Crow

      I've attached a workbook that can help show what I'm talking about. It's a good start, but not quite there. This data isn't from the real application I need these calculations for - I had to change the subject matter entirely.

       

      The goal of the workbook is to show a basketball coaches potential of points per player position and shot type. I want this to be calculated by taking the max number of points per shot type and player position per coach.

       

      Ex. Collin has a max of 2 points in the point guard category and those are layups. And he has a max up 3 points for the Center category; 2 points are layup and 3 points are 3 pointer.

       

      And then multiplying the max numbers from above by the amount of players in that position category.

       

      Ex. Collin has 2 players in center category and the max for centers were 3 points so at 100% potential, a center on Collin's team should get 6 points. And only 2 points for point guard. (2 points max * 1 point guard)

       

      There's a parameter that will change the potential percentage.

       

      There is also a tab that will show missed opportunities. This is when a team is not reaching their potential.

       

      I hope is enough information to help, but if not then maybe I can share with the real application data.

        • 1. Re: LOD and Max Calculations to Show Potential
          Michel Caissie

          Nathalie,

           

          check in the attached.

           

          If I understand correctly, you can get the  max points per position per coach  with

          {FIXED [Coach First Name],[Player Position]: MAX([Points])}

           

          Next  you get the number of players per position per coach with

          {FIXED [Coach First Name],[Player Position]: COUNTD([Player Number])}

           

          The potential becomes

          [Players(PerPositionPerCoach)]*[Points(maxPerPosition)]*([Percentages]/100)

           

          Michel

          • 2. Re: LOD and Max Calculations to Show Potential
            Natalie Crow

            Wow, awesome. Thanks so much Michel!

            So basically I was just adding unnecessary dimensions to my LOD?

            • 3. Re: LOD and Max Calculations to Show Potential
              Michel Caissie

              Basically.

               

              The dimensions you put in the LOD is based on what you want to group by. So for all the rows having the same group of   coach-position (or whatever dimension you put in the lod)  you aggregate those rows.

               

              For example, in your dataset a coach can have  1 to many teams,  and a team can have  1 to many coaches.

              So if you would need to compute the max not only per coach but also per Team, you would need to add the dimension Team in the lod.

              And if you want to compute the max only per Team, then you would need to add Team but remove the coach from the lod.

               

              Also, in Max Points,  you did the MAX of the lod  with a SUM inside the lod. Actually you need the MAX inside the lod.

              And if in your real scenario  you have more than one record for each  player,  then you would need to replace Points in the above calculation  with  {FIXED  [Player number]: SUM(Points)}.

              So, you get the Sum points per Player,  then you get the MAX of that SUM per  coach,per position.

              1 of 1 people found this helpful
              • 4. Re: LOD and Max Calculations to Show Potential
                Natalie Crow

                Sweet, thanks! Also, why did you do MIN(Potential) when you dragged it into the text mark?

                • 5. Re: LOD and Max Calculations to Show Potential
                  Michel Caissie

                  If you look in the ValidateData sheet, you can see that a lod is a bit like adding a new dimension  where the value is the same for every row of the group, so you get duplicates.

                  In your example, since you have the highest detail in the view  (a single coach and player position and shot Type in the view), and a single row per group,  you could use whatever aggregation and get the same numbers.

                   

                  But in a lot of cases, depending on the granularity of the view, you don't want to SUM the duplicates. So you need to use either  MIN,MAX or AVG  on a lod.

                  Really depends on the scenario.

                   

                  For example in the added ValidateDate2, if you would remove the coach from the filter, each group of position-type  would have more than one row.

                  You can see that  MIN or MAX of potential gives the same value, while SUM gives  the Potential * Nb Record.

                  1 of 1 people found this helpful