cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
paulotv Frequent Visitor
Frequent Visitor

Calculating the Difference between two averages

Hi there, I am relatively new to Power BI ( I am using Version: 2.57.5068.721 64-bit (April 2018).

 

I want to show the variance between two averages for gender by year and group - my data table is below & my desired output.

 

I've tried quick measure, measures & new columns but (so far) I've failed. Can anyone help?

 

Thanks in advance.

Paul

 

 

datatable.PNGdesired output.PNGWhat I have.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Ross73312 Super Contributor
Super Contributor

Re: Calculating the Difference between two averages

Step 1:  Create a Measure that simply creates an average, ignoring Gender and Timeframes.  This could look something like:

 

Average Measure = AVERAGE('YourTable'[Score])


Step 2: Create a measure that cares about Gender:

Male Average = CALCULATE(
	[Average Measure],
	'YourTable'[Gender] = "Male"
)

The female version should be easy to work out.

 

Now you can place these gender meeasures on a visual that has the Year & group as the context.


   

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

Proud to be a Datanaut!


   


View solution in original post

3 REPLIES 3
Highlighted
Ross73312 Super Contributor
Super Contributor

Re: Calculating the Difference between two averages

Step 1:  Create a Measure that simply creates an average, ignoring Gender and Timeframes.  This could look something like:

 

Average Measure = AVERAGE('YourTable'[Score])


Step 2: Create a measure that cares about Gender:

Male Average = CALCULATE(
	[Average Measure],
	'YourTable'[Gender] = "Male"
)

The female version should be easy to work out.

 

Now you can place these gender meeasures on a visual that has the Year & group as the context.


   

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

Proud to be a Datanaut!


   


View solution in original post

paulotv Frequent Visitor
Frequent Visitor

Re: Calculating the Difference between two averages

Ross, a big thanks for your post & direction. I created a fourth measure to deduct the individual gender measures from each other & it worked a treat.

 

I have a question though.  How does the measure handle page filters? My data is split by seasons & I have selected the summer seasons only on this page. Is the measure smart enough to pick that up? Do you know?

 

Thanks

Paul

Ross73312 Super Contributor
Super Contributor

Re: Calculating the Difference between two averages

Measures are always context sensitive, unless you explictly write it to ignore the context.

 

So when your measure was used in a table or matrix, the context is all of the column and row context, as well as any filters on the visual, page and report.


   

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

Proud to be a Datanaut!


   


Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 80 members 1,326 guests
Please welcome our newest community members: