4 Replies Latest reply on Sep 18, 2018 10:23 AM by Joe Oppelt

    Add Calculated field at Data-Source Filter


      Hi there,


      I need to exclude certain records from my calculation. Please see attached workbook. I need to exlude the underlying records which are, say, less than 10%.


      See 'By Title' worksheet. The records less than 10% are skewing my totals in the '% by Year' worksheet.


      I thought I could just add my measures at Datasource filter but they dont show up in the Edit Data source Filter option.


      Any ideas?


      Much apreciated.



        • 1. Re: Add Calculated field at Data-Source Filter
          Joe Oppelt

          I'm trying to figure out what you need there.


          What are you excluding?  Whole years?  Countries?

          • 2. Re: Add Calculated field at Data-Source Filter

            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?



            • 3. Re: Add Calculated field at Data-Source Filter

              Please let me know if need any clarification. Not sure if my last post made any more sense?

              • 4. Re: Add Calculated field at Data-Source Filter
                Joe Oppelt

                (V 10.5 here)

                See attached,


                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%.