Forum Discussion
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 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
4 Replies
- Ashish_Mathur
Super User
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.
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.
