4 Replies Latest reply on Jul 21, 2018 11:37 AM by Ken Flerlage

    Alternatives to using 'Context' for user filters when using LOD calculations

    Daniel Bolinson

      Hi there,

       

      I am trying to figure out how to allow user filters when applying LOD calculations to aggregate some measures. In this case, we have many ‘regions’, and each region has many ‘occupations’. Each Occupation has a a salary. I have this data for a few years.


      I am interested in comparing regions based on the average values associated with the occupations in these regions. For example, how does the average salary in that region compare to the state with the highest average salary?

       

      I am using a FIXED calculation to aggregate the average salary data. In other words I have two calculations:

       

      Average Salary in Region = {Fixed Year, Region: avg(Salary)}

       

      Maximum of the Regional Salaries: {Fixed Year: max([Average Salary in Region])}

       

      All good, but now here’s the kicker: I want the user to be able to filter ‘occupations’ and have these recalculate for only the selected occupations. Obviously I am ignoring the Occupation field in these LOD calculations. If I add occupation to the first Fixed calculation (Average Salary in Region) then I am going to get erroneous results. I think I basically need to have the Fixed calculation ignore filtered values, but I can’t figure out how to consider a subgroup of occupations in these calculations. I considered a parameter but need to be able to look across many occupations at once, based on user input. If there was some way to have a Set managed on a user on a dashboard, I think I could make that work, but there’s no way to set up sets with a nice UI that I’m aware of.

       

      Also, this prevents us from looking across years (say I wanted to look at 2015-2017), which we might like to do.


      So far, I am using a context filter, which works, but I understand that's bad practice. I have two questions:

      - Is using context filters for this actually that bad?

      - Are there any alternatives?

       

      Basic dummy data is attached, in practice data set is much bigger.

       

      Thank you very much!

       

      Dan