2 Replies Latest reply on Sep 8, 2016 1:40 PM by Shipra Manchanda

    Reference line based on Avg

    Shipra Manchanda

      Hi,

       

      My data looks likes below. Its a sample version. Each Id can either trace to an Advertiser or Network.

       

          

      IDCategory BrandRating
      5AdvertiserWalmart48
      5AdvertiserTrader's Joe56
      88NetworkFox12
      88NetworkFox78
      287AdvertiserQtips23
      287AdvertiserTesla98
      287AdvertiserSephora45

       

      I want to create a following view:

       

      I need to create a Horizontal bar chart with refernce line. I have put Brand on Rows and Avg. rating on Columns. The filter is set to Category- Network

      Is it possible to add a reference line that is the average of 'Rating' when I select a particular Brand under Advertiser category. i.e. If I select Walmart the average 'Rating' value is a reference line on the bar chart. And I can select only Brands under Category advertiser because Brand under Network category are a part of bar chart.

       

      Please let me know if you need any other info.

       

      Thanks

        • 1. Re: Reference line based on Avg
          chris.moore.11

          Hi, Is the attached what you are looking for?

           

          I imagine there are easier solutions to this but what i did was...

           

          Duplicated the data source.

          Made a field called "Link" in each source that was just a constant value and used that as the link to blend the 2 sources.

          Made a parameter to choose the advertiser.

          Made a calculated field in the duplicated data source that only returned the ratings for the advertiser chosen in the parameter.(case [Brand] when [Parameter 1] then [Rating] else null end)

          Dragged that calculated field into the marks card.

          Added a reference line using that field.

           

          This way the filters you choose for your bar chart wont apply to the average coming from the duplicated data source.

           

          Hopefully this helps.

          • 2. Re: Reference line based on Avg
            Shipra Manchanda

            Thanks a Lot. It worked like a charm.