Forum Discussion

Apaco89's avatar
Apaco89
Frequent Visitor
7 years ago

Average of Distinct values

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 ...
Need Help
Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
7 years ago

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's avatar
Apaco89
Frequent Visitor
7 years ago

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