How to compute multiple SUM Values of a table based upon condition and then take Average of all SUMs

[HassanAshas]

I have a dataset of employees working on month-to-month basis. 

1. It is possible that every employee might be working on multiple projects in one month (separate row is stored for each project for each month) 
2. Allocation % is stored for every Employee. This means the amount of total time employee spent on a specific project in a specific month (For one month record, total allocation % can't exceed 100% (1) ).
3. Month Difference is the difference in the number of months from TODAY's month to that specific row's month.
4. Project Code is a bit irrelevant for this problem. 

   Now, what I need to do is actually add a Slicer on Month Difference, and based upon that, I want to calculate my records.

   I have two KPIs, 

   1. Distinct Count of all the Employees (If the slicer is set to 2 months, then it shows distinct count of all employees within the last 2 months) and etc. 
   2. Total Allocation completed by all the Employees. This is where the problem is. 

      My second KPI basically tells that how much of the time all the employees were allocated. For example, in the month of November, I had 3 Employees and their total sum of allocation was 2.35. 

      

      
      
      For now, this is the formula that calculates the total allocation sum, 

       Total Allocation Utilized = SUM(Competency[Allocation])
      
      

      As you would have guessed, this works fine if the Filter is set to current month only. But, when I want to do the same for last 2 months, it doesn't work.

      What it does is it sums up all the Allocation values. This is ok for one month, but not for multiple months.
      
      

      

      Now, I know the logic of what I want to do in programming terms, but unable to replicate the same logic in DAX. 

      I need to do something like this, 

      1. For each month, I want to sum up the allocation values for all the Employees
      2. Once I have the SUMs of all the months, I want to take an average of those SUMs with the number of months selected. 

         So, if filter is set to "2", then it will take three SUM values of three months (current and last two months) and then at the end, add up those sum values and divide them by 3 (or take their Average Value. I know I have to use AverageX function but I am unable to create those "n" number of SUMs in between) 

         Can anyone help me out in this? Thank you. 

You can download the Power BI File from this link: https://drive.google.com/file/d/10QSUlZiYyISuQj6O-9Zcym2PO4OlUwGq/view?usp=sharing

[Greg_Deckler · Accepted solution]

@HassanAshas Try:

Measure = 
    VAR __Table = 
        GROUPBY(
            'Competency',
            [Month],
            "__Allocation",SUMX(CURRENTGROUP(),[Allocation])
        )
    VAR __Result = AVERAGEX(__Table,[__Allocation])
RETURN
    __Result

[HassanAshas]

Oh my God! That worked like a Charm!! 
Going to understand how is this working now on a paper. Thank you so much for such a quick and an AMAZING response!