8 Replies Latest reply on Aug 23, 2016 11:31 PM by Patrick Myles

    Finding median of table calculated values

    Patrick Myles

      Hey All - I'd like to do the following: find the median value of a set of rows which are using a table calculation (the values circled below).  each value is using a 'percent of total' table calculation and i'd like to have a row that shows the median value of these values for each category (eg. the median for cat1, cat2 etc).  is that possible? I've attached the workbook. 

      Ideally I might like to hide the actual countries leaving only the median value as a row (or chart)

      Thank you for any help!

      Patrick

        • 1. Re: Finding median of table calculated values
          chris.moore.11

          There may be a more simple way to do this, im not sure.

           

          You could calculate the percentages using a LOD calculation with a calculated field (instead of a table calc) and then take the median of that. This may reduce some flexibility depending on what you're trying to do later on.

           

          "Percentage calc" is a calculated field in this workbook that the median is being taken for now.

          1 of 1 people found this helpful
          • 2. Re: Finding median of table calculated values
            Patrick Myles

            Hi Chris,

            I'm having trouble with this.  could you tell me 1. why the percentage calc field is in the dimensions pane? and 2. why is there no [country] as a dimension value within FIXED area of the percentage calc?

             

            thanks

            Patrick

            • 3. Re: Finding median of table calculated values
              Isaac Mickey

              Hi Patrick

               

              Would a reference line in a bar chart do the trick?

               

              See attached

              • 4. Re: Finding median of table calculated values
                Patrick Myles

                Hi Isaac,

                Thanks.  The ref lines are not what I had in mind,  In fact I want to produce the median values to have them by themselves in a table (or be charted).  So ideally, i'd have 1 one of the median values (calculated over the countries) per category. 

                I'm struggling to get this working.

                 

                Regards

                Patrick

                • 5. Re: Finding median of table calculated values
                  Isaac Mickey

                  Ok I think I understand what you're trying to do now.  I still think a reference line is the only thing that'll work though unfortunately.  Your % values are already aggregated and so a median can't be calculated off these aggregated measures.

                   

                  I also think your month is clouding the picture, take it out and make it a filter instead you get the average result by country by category.  With a reference line added based on median you should have all the information required.  If you don't want to show the bars for each country just change them to white

                   

                  At the very least with this option you now know the values you're looking for!  The only other alternative I can think of is to hardcode these in to a new dataset!

                  • 6. Re: Finding median of table calculated values
                    Patrick Myles

                    hey Isaac,

                    Yeh, the lines are nice (and useful for certain things), however ideally i'd love to have the median values as values i can plot in other charts particularly as they change over my time series.  But thank you so much for your help!

                    • 7. Re: Finding median of table calculated values
                      chris.moore.11

                      I just had it as a dimension so it wouldn't auto aggregate when i put it in the row/columns.

                       

                      What the fixed calculation is doing is taking a total over the two fields listed in it. So in this case it is taking a sum for each unique grouping of category and date. Basically in your example it sums all the countries up for each date and category. That way you can use it as the denominator of the percentage. Then the calculated percent is country/sum(countries). Hopefully the attached will help show what its doing.

                       

                      The problem you will run into with this is "fixed" is calculated before any dimension filters. So any filter you would want to impact that calc (like country for example) you would need to right click and set it as a context filter. The other option would be try and use "include" instead of fixed.

                       

                      This workbook has three sheets that may help explain, sorry for the poor explanation.

                       

                      Theres other people who have already explained lod calcs better than i can. ex. Level of Detail (LOD) Expressions | Drawing with Numbers

                      1 of 1 people found this helpful
                      • 8. Re: Finding median of table calculated values
                        Patrick Myles

                        Hi Chris - this is very helpful indeed.  I see what's going on now. 

                         

                        I realised also that I actually wanted to show the total sum of the country (not the category) - so for example the fixed sum should equal sum of each category in that country.  That seems an easy fix however as I replaced 'category' with 'country' in the LOD calculations. 

                        but now my question is how to enforce it's only calculating the median part over a 'set of countries.  I see you have done something with 'include' and 'context filter' but not sure it's working. 

                        For example - if you only wanted the median value to be over 3 countries (australia, japan and new zealand), how could you do it?

                         

                        thanks again!

                        patrick