2 Replies Latest reply on Aug 11, 2018 7:37 AM by Abhaas Mohan

    Dynamic reference lines based on selected geo filter

    Abhaas Mohan



      I have a dataset (attached), which is based on some data points with respect to geographic field. My data source has roughly following format


      DistrictX Volume# X


      In the attached workbook, in sheet Avg Sales XYZ, the worksheet displays the average sales (Volume/#) for two companies (X & Y). This sheet shows current values for the filter selected (State level, District Level).


      I have a static reference line which reflects the national average (Total X Volume/Total # X), and I am trying to put another reference line, which I wish to be dynamic, showing me average based on geographic filter currently active (State level). I have used the following formula for each company's calculated field.


      SUM(IIF([States]='Bihar',[X Volume],0))/SUM(IIF([States]='Bihar',[X #],0))


      What I cannot figure out, is

      1) Why this formula does not return 0 value (hence reference line at 0 if 'Bihar' State is not selected on the LHS Map dashboard or any other state is selected)

      2) If I can use a better formula to help me display this dynamic reference line which shows up and dependent on the Active State selected in LHS Map Dashboard ( IF 'Rajasthan' is selected, then the reference line parameter 'State Ref' takes value of average for that particular State


      Attaching Packaged workbook for your reference.

        • 1. Re: Dynamic reference lines based on selected geo filter
          Jim Dehner

          Not certain I understand the total issue but you had the ref line set to the state  parameter for the X avg which will return a constant value - resetting it to the Agg fro the calculated field will return a 0 when Bihar is filtered out of the data





          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          1 of 1 people found this helpful
          • 2. Re: Dynamic reference lines based on selected geo filter
            Abhaas Mohan

            Thanks Jim! @

            Attaching the updated workbook and clarifying the issue -


            Basically, the idea is that if no state is selected, the National avg & State Avg line should coincide, and if a state is selected, the state avg line should move accordingly, and when a district is further selected, the state avg line becomes static as well.


            From what I have now, with the National & State Ref line linked to calculated fields NatProdLine & StateProdLIne (which uses FIXED : (Geography Level) to calculate that level's avg) in Image below


            Issue: When geo filters are de-selected, ideally, the bars and the two ref line should coincide. However, they don't.



            Which doesn't make any sense, as the total avg should be 161 for even State Avg (If not filters are active). I can't even locate where this 155 value is coming from.


            Let me know if you need me to clarify.