Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
swong
Helper II
Helper II

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

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

View solution in original post

19 REPLIES 19
v-ljerr-msft
Employee
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. Smiley Happy

 

Regards

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

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.

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])

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

I used to have slicers, but I took them off for simplicity/to figure this first issue out, but those columns weren't being used anywhere else.

 

Here's my dashboard: https://msit.powerbi.com/view?r=eyJrIjoiNzA2MDdiODQtZTNhNC00NTNkLWExM2MtNGJhMGY2YWU1ZTRiIiwidCI6Ijcy...

 

Why would the slicers affect the display?

Hi @swong,

 

A little weird! So could you share a sample pbix file(with just some sample/mock data) which can reproduce this issue? So that we can help further investigate on it. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading. Smiley Happy

 

Regards

Thank you so much! Here's the OneDrive link: https://microsoft-my.sharepoint.com/personal/t-sewong_microsoft_com/_layouts/15/guestaccess.aspx?gue...

 

Let me know if the permissions don't work.

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

I have a follow-up question -- would it be possible to set the "Overall Line" to be the average revenue for a single color, and for the "Individual" line to be the average line for  single account within that color?

 

More broadly, is it possible to have two lines on the same line graph affected by different slicers?

Hi @swong,



I have a follow-up question -- would it be possible to set the "Overall Line" to be the average revenue for a single color, and for the "Individual" line to be the average line for  single account within that color?

 

More broadly, is it possible to have two lines on the same line graph affected by different slicers?


Yes, the formula below should work in this scenario. Smiley Happy

 

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

r6.PNG

 

 

Regards

Hello again!

 

Whe I add date as a slicer for my table, it works fine.

 

However, I'd like to have two line graphs both affected by the same date slicer. I thought to do this by creating and aux table, where each distinct date has a distinct value, the creating a relationship between Date1 of Table1 and the aux table, and a relationship between Date2 of Table2 and the aux table.

 

The slicer based on the aux table's Date column affects both individual lines on my two line graphs, but not the overall line. Is there a way to make this slicer affect all lines?

Hi @v-ljerr-msft,

 

Checking in about this question.

Hi @v-ljerr-msft,

 

Should I post this in a new question?

Oh I see! Thank you so so much for all of your help! 🙂

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.

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?

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.

Anonymous
Not applicable

Try:

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

SomeMeasure = SUM('RevenueTable'[Revenue])

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.