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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    7 years ago

    Hi,

     

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

    • Apaco89's avatar
      Apaco89
      Frequent Visitor
      7 years ago

      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