cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
swong Regular Visitor
Regular Visitor

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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Average over time vs. sliced average

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. Smiley Happy

 

r4.PNG

 

Here is the modified pbix file for your reference. Smiley Happy

 

Regards

19 REPLIES 19
Ross73312 Super Contributor
Super Contributor

Re: Average over time vs. sliced average

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


swong Regular Visitor
Regular Visitor

Re: Average over time vs. sliced average

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 Regular Visitor
Regular Visitor

Re: Average over time vs. sliced average

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.

Ross73312 Super Contributor
Super Contributor

Re: Average over time vs. sliced average

Try:

AllMeasure = CALCULATE(
	SUM('RevenueTable'[Revenue]),
	ALL('RevenueTable')
)

SomeMeasure = SUM('RevenueTable'[Revenue])

   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


v-ljerr-msft Super Contributor
Super Contributor

Re: Average over time vs. sliced average

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 @Ross73312 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. Smiley Happy

 

Regards

swong Regular Visitor
Regular Visitor

Re: Average over time vs. sliced average

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

 

ExampleData.PNG

 

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: ExampleDataWrongViz.PNG

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.

 

ExampleDataOverallAvgViz.PNG

Ross73312 Super Contributor
Super Contributor

Re: Average over time vs. sliced average

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.


   

              Did I answer your question? Mark my post as a solution!
       

Proud to be a Datanaut!


   


swong Regular Visitor
Regular Visitor

Re: Average over time vs. sliced average

Oh I see. I understand that definition, and now my formula is

 

Overall = CALCULATE(AVERAGE(Sheet1[Revenue]), ALLEXCEPT(Sheet1, Sheet1[Date]))

 

But this still gives me the same graph with the straight black line.

 

For reference, my individual formula is:

 

Individual = AVERAGE(Sheet1[Revenue])

v-ljerr-msft Super Contributor
Super Contributor

Re: Average over time vs. sliced average

Hi @swong,

 

What columns are you using as Slicers on the report? Are these columns in another table? If that is the case could you try using ALL function with these columns in the measure like below to see if it works? Smiley Happy

Overall =
CALCULATE (
    AVERAGE ( Sheet1[Revenue] ),
    ALL ( Sheet2[Location] ),
    ALL ( Sheet3[Device Type] )
)

 

Regards

Helpful resources

Announcements
Community Highlights

Community Highlights

Find out what's new in the Power BI Community!

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 316 members 3,304 guests
Please welcome our newest community members: