9 Replies Latest reply on Jan 13, 2017 4:26 PM by John Linville

    Create a calculated field using filters and overlapping sets

    John Linville

      Hi,

       

      I am trying to create a calculated field using the results of a filter (based on sets that overlap).

       

      I have created a workbook in Superstore (attached) that illustrates what I am trying to accomplish:

       

      I have created a couple of sets from 'States' ('Joe' and 'Chuck') that are managers and a combined set ('George') that is their regional manager. I then created calculated fields ('Manager' and 'Regional Manager') from those sets to use as filters. So far, so good.

       

      Now, I want to be able to ONLY show the Average Profit  of 'Joe' as a reference line when ONLY 'Joe' is selected, i.e., if 'Chuck' or 'All' is selected, the line is not shown. I created a calculated field ('Average Profit') that is the 'Window_Avg(SUM)[Profit])' that is fine. I then created a calculated field that is based on 'Average Profit' that is that profit when 'Joe' is selected and added that as a reference line--that works great. If I DEselect 'Joe', the line disappears, like I want. However, if 'Joe' AND 'Chuck' (or 'All') are selected then the line still appears because the States for 'George' include 'Chucks', as well. I can't figure out how to get the line to ONLY show when ONLY 'Joe' is selected and nothing else in the 'Manager' filter.

       

      As a workaround, I could create a parameter and turn a worksheet on and off that shows the line or not, based on a selection of the 'Joe' parameter, but that would be a lot of work to do for each manager--plus, the recalculation time for a dashboard increases with each hidden sheet AND for each hidden sheet I lose a little screen real estate.

       

      If anyone can help, I would really appreciate it!

       

      [BTW, I know this scenario doesn't make much sense--it is only a proxy for one that I am working on on real data that actually DOES... ]