Forum Discussion

danlemarquand's avatar
danlemarquand
New Member
9 years ago

Average over a category

I need to calculate an average value over a category so that I can then reference it in another calculation. I need my calculation to be dynamic enough so that when I select a second category, the orginal calculation only takes into account the selected population.

 

Essentially I need to be able to do the equivalent to an averageif in excel, but that is dynamic based on selections. 

 

For example, assume I want to calculate the average salary for each location in a dataset and store this information so that I can then calculate a further average based on male and female. Initially this would calculate over the whole population, but if I then selected a department, for example Finance, I then need to have the average salary at each location WITHIN finance. 

 

I have read lots about ALLEXCEPT and ALLSELECTED, but not sure how to apply this to my problem. 

 

 

 

 

5 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi danlemarquand,

     

    Could you post your table structures with some sample data and your expected result, so that we can better assistant on this issue? :smileyhappy:

     

    Regards

    • danlemarquand's avatar
      danlemarquand
      New Member
      9 years ago

      So let's assume I have data like this then I need to achieve the following:

       

      Calculate the average salary for each location and paste this into each row. Let's call this "location average". So in excel this would be an averageif over the locations. 

       

      I then need to calculate the difference between male and female "location averages". So take that new column and calculate the average over it for men and women and work out the difference. 

       

      As part of this, if I select only department = HR, I need the data to filter down so that the "location averages" are recalculated based on only the selected population. 

       

      Does this make sense? I suspect the last point is covered purely by the mechanics of the tool, but I need to get confirmation on the first two steps before moving on. 

       

      Thanks!

       

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Hi danlemarquand,

         

        So your final goal is to calculate the difference between male's and female's average salary dynamically based on Location and Department(user selected on the report)?

         

        If that is the case, you should be able to use the formulas below to calculate average salary for male and female separately first.

         

        Average Salary Male = CALCULATE(AVERAGE(Table1[Salary]),Table1[Gender]="Male")
        Average Salary Female = CALCULATE(AVERAGE(Table1[Salary]),Table1[Gender]="Female")

         

        And use the formula below to calculate the difference between male and female.

         

        Salary Difference Between Male and Female = [Average Salary Male] - [Average Salary Female]

        Then you should be able to show the [Salary Difference Between Male and Female] measure on the report, and it will calculate dynamically against the selected Location and Department.

         

         

         

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

         

         

        Regards