Forum Discussion

xiumi_hou's avatar
xiumi_hou
Post Partisan
7 years ago

Question Regarding calculate group average

Dear allI was trying to calculate different indicators' average by group. The avergae is total number of cases/total number of people. When I put my dax function as below: 

Count_Average_Closed_Cases =
CALCULATE(
DIVIDE(count(f_Cases[CaseNumber]),count(d_users[id])),
f_Cases[Status] = "Closed",
USERELATIONSHIP(f_Cases[Closed_date_c],d_date[Date])
)
The result show exactly not the group average, but the single people's number based on my filter. Any suggestion? Thanks in advance. 
Need Help

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    7 years ago

    xiumi_hou can you share the sample data and expected result and also how you want it to behave when you filter the data?

    • xiumi_hou's avatar
      xiumi_hou
      Post Partisan
      7 years ago

      Hi Parry, Thanks for asking.

      Actually I would like to separate into two parts: 

      1.  I have Five scliers: Year/Month/Group/User Name/Indicators

          The goal is to monitor for a single month, what is the perforamce for user A's indicator A (I want to use actual value compare with group average as well as the target value).

          I have created several measure for the actual value of each indicator, however, I do not know how to link the target value and how to calculate the average within group and link back to the actual value? below is the sample data:

       

       

       

       

       

       

       

       

       

      2. The second condition is exactly same with the previous one, the quesiton is when I calculate average, I use function: 

      Count_Average_Closed_Cases =
      CALCULATE(
      DIVIDE(count(Indicator ID),count(d_users[USER id])),
      f_Cases[Status] = "Closed",
      USERELATIONSHIP(f_Cases[Closed_date_c],d_date[Date])
      )
       
      The question is I tried to sum up to count the number of single indicator and divide by the number of user to calculate the group average, but i do not know how to seperate by group? And when I select a fitler, the average is exactly the same as the person I selected.. Thanks!! parry2k 
      • parry2k's avatar
        parry2k
        Super User
        7 years ago

        xiumi_hou im not fully clear about your requirement. Could you plese share data in excel file and put expected result in a sheet explaining calculation and will get you the solution.