Average of Distinct values

[Apaco89]

Hello, Can anyone help me with one of the thing I am trying to do. 

I am trying to find the Average allocation % for a resource and I need that to calculate for a selected date range.  I wrote a dax 

Total Resource Allocation = AVERAGEX ( VALUES('Resource Allocation Forecast'[Resource NameId]),CALCULATE(AVERAGE('Resource Allocation Forecast'[Allocation %]))) 

for calculating the average and I am not getting the result I need.

Example below. 

Allocated projects for a resource in each montthSum of allocated hours in each month for a Resource

I want to calculate average allocated hours from sep to dec . "85+80+80+35/4".  

Appriciate your help on this. 

Thank you 

[Ashish_Mathur]

Hi,

Try this measure

=AVERAGEX(SUMMARIZE(VALUES(Calendar[Month]),[Month],"ABCD",SUM('Resource Allocation Forecast'[Allocation %])),[ABCD])

Drag this measure to a card visual.  In the slicer/filter, select a particular resource and year.  Ensure that the Year is dragged from the Calendar Table.  There should be a relationship from the Date column of the Resource Allocation Forecast table to the Date column of the Calendar Table.

[Apaco89]

Thank you for helping me .

I tried the dax but, this giving me a in correnct average value 

Card Value

Average = AVERAGEX(SUMMARIZE(VALUES('Date'[Calendar Month Name]),[Calendar Month Name],"ABCD",SUM('Resource Allocation Forecast'[Allocation %])),[ABCD])

How can we sum the distinct values ? I hvae multiple entries for a single resouce accoriding to their start and end date. 

Below is the DAX which I am using for calculating the total of project hours for emplyess in every month/week. incase if this helps to understand better.

Total Allocation = SUMX(DISTINCT('Resource Allocation Forecast'[ProjectId]),CALCULATE(AVERAGE('Resource Allocation Forecast'[Allocation %])))

Thanks, 

Apaco

[Ashish_Mathur]

Hi,

Describe your question in detail.  Share the link from where i can download your PBI file and show the expected result.

[Apaco89]

Hello, 

Below is a snapshot of my table structure. In my graph i am dispaying the weekly sum allocation for each project and in the other graph I am showing total allocation for each resource in month/week ( I have attached two graph snapshot in my question).

Table Snapshot

Now , I am trying to show the  total average allocation percentage of a resource (all projects) in a selected date range. 

If you see my graph in the questions, In september total allocation is 85 and in Oct & Nov, total allocation is 80, And in december it is 35. I need a score card which shows the average allocation of resource in 4 months. (85+80+80+35)/4

Please let me know if this helps. 

Thanks