5 Replies Latest reply on Aug 2, 2018 9:55 AM by ljubomir stajic

    Average of Measures

    ljubomir stajic

      Hi All,

       

      I have a graph that shows year-over-year growth by different expense categories.

      Is there a way to get an average line that combines all these measures? Please note that this also has to work with the active filters on the sheet.

       

      Thank you so much.

       

        • 1. Re: Average of Measures
          tian.yang.4

          Hello maybe you can try to create dual axis (grey line stands for the avg)

           

          1.jpg

          • 2. Re: Average of Measures
            Ken Flerlage

            I think this should be possible. I took each of your measures and turned them into actual calculated fields. I don't know what to call them, so I just called them C1, C2, C3, C4, and C5. I then created an Avg calculated field which does this:

             

            ([C1]+[C2]+[C3]+[C4]+[C5])/5

             

            Then I added that measure to the view.

             

             

            Is this what you're looking for? If so, I've attached a workbook.

            1 of 1 people found this helpful
            • 3. Re: Average of Measures
              Tushar  More

              Hi,

               

              Hope I understood your requirement correctly. I have not downloaded your as it is quite heavy. But, here an idea using the superstore data set.

               

              Create a calculated field that gives avg of these measure.

              Use this field along with your actual measure and also create dual axis to combine them,

               

              Hope that helps.

               

              ~Tushar

              • 4. Re: Average of Measures
                ljubomir stajic

                Hi Ken,

                 

                The problem with your formula is that it breaks when one of the measures is null (data filtered out). The formula has to be a little more dynamic than that.

                 

                Thanks so much.

                • 5. Re: Average of Measures
                  ljubomir stajic

                  Hi Tushar,

                   

                  I'm not sure you got the issue--the formula you posted is an average sum for each region by year. In other words, you only have 1 measure--the Sales.

                   

                  I'm trying to find an average of multiple measures.

                   

                  Using your example, the formula would look something like the one copied below. Unfortunately, my attempt contains an error. Perhaps you can help fix it.

                   

                  My main concern is that sometimes one of the measures can disappear--The formula for Average has to be dynamic enough to take this into account.

                   

                  Thanks so much.