Forum Discussion

paulotv's avatar
paulotv
Frequent Visitor
8 years ago
Solved

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

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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.

    • paulotv's avatar
      paulotv
      Frequent Visitor
      8 years ago

      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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.