Forum Discussion

Mist's avatar
Mist
Frequent Visitor
10 years ago
Solved

Plot a filtered value compared to an average across unfiltered values

Let's say I have monthy sales data for a few companies over one year. I want to plot the average sales across all companies. Easy right! Then I want to add one companies sales to the same plot to allow a comparison to the average. How do I do that?

 

Here's a picture of what I have now:


 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

I want to show the average line (second line graph) on the first line graph. When I click on CompanyA in the histogram I would like to have a line graph showing just CompanyA data AND the average across all companies.

 

Is that doable?

 

Thanks

  • Mist's avatar
    Mist
    10 years ago

    Here's how I solved it. Instead of separate measures for each company I just created one measure that's the result of the highlighted company:

    SalesSelected = CALCULATE(AVERAGE([sales]), ALLSELECTED(sales[company]))

    The average line is the same as chrisu suggested:

    AvgSales = CALCULATE(AVERAGE([sales]), ALL(sales[company]))

    Thanks chrisu for puting me on the right track.

3 Replies

  • chrisu's avatar
    chrisu
    Icon for Responsive Resident rankResponsive Resident
    10 years ago

    There's probably a better solution than this, but this has worked for me:

     

    1. Because you can either have a legend OR multiple measures on a line chart, you will have to define a measure for each company, like this:

     

    CompanyASales = CALCULATE(SUM([sales]), [company] = "CompanyA")

    CompanyBSales = CALCULATE(SUM([sales]), [company] = "CompanyB")

     

    etc.

     

    2. Then create an average sales measure using ALL to override any filters on company:

     

    AverageSales = CALCULATE(AVERAGE([sales]), ALL([company]))

     

    3. Add all of those measures to the values area of the line chart (you will have to remove company from the legend first).  

    • Mist's avatar
      Mist
      Frequent Visitor
      10 years ago

      Hi chrisu, thanks for responding - I was excited when I saw your post as I thought "Yes, that's going to work!". But I don't think it quite does what I wanted. It gets me lines for each company on the chart plus a line for the average across all companies on the same chart which is great and a step further that I'd gotten before (kudos!). I was hoping though that when I filter by CompanyA by clicking on the histogram then ONLY CompanyA AND the average line would remain on the plot, allowing the user to focus on just the difference between the company of interest and the average. Any ideas if this is possible? Here's a pic of where I'm at now.

      • Mist's avatar
        Mist
        Frequent Visitor
        10 years ago

        Here's how I solved it. Instead of separate measures for each company I just created one measure that's the result of the highlighted company:

        SalesSelected = CALCULATE(AVERAGE([sales]), ALLSELECTED(sales[company]))

        The average line is the same as chrisu suggested:

        AvgSales = CALCULATE(AVERAGE([sales]), ALL(sales[company]))

        Thanks chrisu for puting me on the right track.