
1. Re: Finding median of table calculated values
chris.moore.11 Aug 22, 2016 9:48 AM (in response to Patrick Myles)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.

finding medians 2.twbx 12.6 KB

finding medians 2.twbx 12.6 KB


2. Re: Finding median of table calculated values
Patrick Myles Aug 22, 2016 8:52 PM (in response to chris.moore.11)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 Aug 22, 2016 10:51 PM (in response to Patrick Myles)Hi Patrick
Would a reference line in a bar chart do the trick?
See attached

finding medians.twbx 20.5 KB


4. Re: Finding median of table calculated values
Patrick Myles Aug 22, 2016 10:55 PM (in response to Isaac Mickey)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 Aug 23, 2016 12:19 AM (in response to Patrick Myles)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!

finding medians.twbx 26.9 KB


6. Re: Finding median of table calculated values
Patrick Myles Aug 23, 2016 5:18 AM (in response to Isaac Mickey)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 Aug 23, 2016 6:37 AM (in response to Patrick Myles)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

finding medians 2 (2).twbx 37.7 KB


8. Re: Finding median of table calculated values
Patrick Myles Aug 23, 2016 11:31 PM (in response to chris.moore.11)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