1 of 1 people found this helpful
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.
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. 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.
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
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!
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
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?