0 Replies Latest reply: Nov 16, 2011 4:38 PM by Josh Campbell RSS

    Set Analysis to Select/Filter by Values in Data

    Josh Campbell

      I just thought I would share something I did for a document I've been working on. I have a 'page' in the document that displays 12 graphs (bar charts) for different types of travel records (air, car, hotel, rail) and within each of those categories I have a graph that displays 'total paid', 'sum of records' and 'avg cost'.

       

      The problem I had was that my data for air and rail is mixed together and only differentiated by a travel mode (A or R). I had to use Set Analysis to properly sum the total cost and number of records for air:

      SUM({$<[Mode]={'R'}>}[Cost])

       

      I used a similar expression for number of records. Then I encountered a problem where the avg cost per record was being divided (the cost for Rail) by all records (not just Rail). I eventually got it to work with the following expression:

      SUM({$<[Mode]={'R'}>}[Cost]) / SUM({$<[Mode]={'R'}>}[Record])