Forum Discussion

swong's avatar
swong
Helper II
9 years ago
Solved

Average over time vs. sliced average

Hi,

 

I'm hoping to use PowerBI to show overall market trends as well as trends in individual accounts. My data has several different slicers, e.g. location, device type, etc.

 

Is is possible for me to show two lines on a line graph, one for the overall marketplace average, and one for the individual account selected?

  • Hi swong,

     

    I have reviewed your shared pbix file. And I find that there is no problem with the formula of the measures. Instead of using Date hierarchies as Axis, you should use the Date column itself as Axis, then it will work as expected. :smileyhappy:

     

     

    Here is the modified pbix file for your reference. :smileyhappy:

     

    Regards

19 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    You could but you'll need to get tricky.  I can think of a few ways to achieve this, so i'll offer one method and if that isn't suitable i can suggest other ideas.

     

    You'll need a single measure that shows everything on the market, using an ALL or ALLEXCEPT depending on what filters you want to constrain this with (i.e. maybe a date range).

     

    Next, you'll need a measure that does the same as the previous, just without the ALL/ALLEXCEPT.  Now, put both those measures onto your line graph.

     

    One issue you might face however, is that doing this you are going to have a shared Y-Axis.  This means unless you are using a function that normalizes data in some way, you might not be able to get much value out of the smaller line.  For example "SUM" data will be more problematic compared to data such as % growth.

    • swong's avatar
      swong
      Helper II

      Thanks for the response!

       

      I'm still fairly new to PowerBI, so I'm not sure what you mean.

       

      I would create a new measure showing my average revenue for everything? How would I include the AL or ALLEXCEPT? Woud I be able to affect both measures with different filters?

      • swong's avatar
        swong
        Helper II

        I tried making some new

         

        measure = ALL('RevenueTable'[Revenue])

         

        but the measure can't be loaded: A table of multiple values was supplied where a single value was expected.

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee

    Hi swong,


    Is is possible for me to show two lines on a line graph, one for the overall marketplace average, and one for the individual account selected? 

    Using ALL/ALLEXCEPT could achieve it as Anonymous has mentioned above.  The formulas below are for your reference. 

    overall marketplace average =
    CALCULATE (
        AVERAGE ( 'RevenueTable'[Revenue] ),
        ALLEXCEPT ( RevenueTable, 'RevenueTable'[Column_Name_that_you_used_for_Axis] )
    )
    
    individual account selected = SUM ( 'RevenueTable'[Revenue] )
    

    If you still cannot figure out the measure to do it, you can just post your table structures with some sample/mock data and your expected result. So that we can better assist on this issue. :smileyhappy:

     

    Regards

    • swong's avatar
      swong
      Helper II

      I think I'm understanding a little more, but I'm still confused about the Column_Name_that_you_used_for_Axis

       

       

      This is my example data. When I used the formulas you suggested (I changed the individual SUM to an AVERAGE because I would like to compare an individual average gainst the marketplace overall), I got this graph:

      I was hoping for th black line to look more like this teal one below, which is the average of the overal marketplace broken down by day.

       

      • Anonymous's avatar
        Anonymous
        Not applicable

        The [Column Name that you used for Axis] is the column that you have placed into the X-Axis area of your graph.  As you have noted, you are getting a graph that is a straight line.  The ALL method ignores all context, thus it doesn't matter where on the graph it is placed, it always gets the same answer.

         

        ALLEXCEPT however, ignores all context except for the context provided in the columns you specify.  So by placing your X-Axis column into ALLEXCEPT, it will take that context into account.