I'm trying to figure out what you need there.
What are you excluding? Whole years? Countries?
My bad, didnt make it clear.
My total percentages in the '% by Year' sheet are very low. That´s because there are some records (Title) which have very low percentage ratings. And majority of these are not really needed. Therefore, I would like to exclude the Titles which have less than 5% ratings from that year.
See 'example2' sheet. These are some examples of the records which should be excluded from the global data source filter. - except for ´the title:
10 Buildings That Changed America in year 2011 and 2012. As these are over 5%.
Once this has been done, I am expecting my figures in the '% by Year' sheet to increase.
Hope this makes better sense?
Players.twbx 1.6 MB
Please let me know if need any clarification. Not sure if my last post made any more sense?
(V 10.5 here)
I made a FIXED LOD for each year at the level of [Title]. For each title, calculate that year's percentage. (I could also have done this at the level of [Title] AND [Year], and then handled the filter calc differently.)
Take a look at the (copy) versions of the three calcs. I just wrapped your logic in FIXED. I plunked each on TEXT on example 2. Notice that the value displays under each year regardless of what year it is calc'd for.
Next look at Calculation1. Here I set a value of 1 if the Title should be filtered out. The ISNULL part says that if there is no value for that year, just look at the other years.
So with this calc, if you put that on filters and exclude values of 1, you'll get all the titles that have at least one year when there is a percentage of at least 5%.
Players_v10.5.twbx 1.6 MB