Forum Discussion

jweave98's avatar
jweave98
Frequent Visitor
7 years ago

Question Regarding Measures

I am trying to create a measure to compare averages for a time period of all users against a given users numbers for that time period. I am using the measure below.

 

 

Percentage by user = DISTINCTCOUNT(new_businessdevelopments[new_businessdevelopmentid])/DISTINCTCOUNT(new_businessdevelopments[_ownerid_value])
 
The measure above works great at the employee summary level, but doesnt calculate as expected at the firm level. The numbers dont add up across the board because not all users (the ownerid) column are inputting records of all types, so the math for the firm average matrix doesnt add up across the board (there are only 37 employees that filled out meetings as oppossed to 40 employees that filled out a record, so the sums are being divided by different ownerID numbers making the values in the matrix not add up across the board properly. Both matrixes for employee and firm are using the same fields, one is set so it wont filter based off the employee name filter. Is it feasible to get the firm average of 65 for Q1 broken down so that the numbers add up to 65? Right now, my equation divides the 392 meeting records by 37 (the unique owner id count for records with meeting as the activity type) as oppossed to what I was expecting which would be 392/40 which would be the total number of unique owner id records for the Q1 time period. 
 
 
Data Modeling

4 Replies

  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    Community Support
    7 years ago

    Hi jweave98, 

     

    I'm not very clear about your requirement. I don't find the 392 and 40 in the screenshot. Please clarify it. 

     

    It would be better if you could share the pbix file. 

     

    Best Regards,
    Qiuyun Yu

    • jweave98's avatar
      jweave98
      Frequent Visitor
      7 years ago

      Thanks for following up, Qiuyun! I cannot share my pbix file because its source is our CRM system and there is information we would not like to share publicly. Incase I was unclear, I have written the following equation to calculate the average number of records created per user and then have used the activity date field that all these records have to determine the average amount of records created by the users contributing to these records by qtr/ year. Everything sums up perfectly at a high level.

       

      DISTINCTCOUNT(new_businessdevelopments[new_businessdevelopmentid])/DISTINCTCOUNT(new_businessdevelopments[_ownerid_value])
       
      When I expand my matrix down a level to break up the average per quarter by activity type (the matrix titled Firm Averages by Qtr by Year), the numbers dont add up across the matrix to the total. Qtr 1 2018 has values 3,3,11,2,4 and 10 which equals 33 while the total for that row says 24. 24 is indeed the right number, since there are 969 total records for that quarter and there are 40 unique employees ids contributing to that number. I have determined the issue is because not all users are contributing all record types in the matrix, so the denomanator has a different value for each activity type (There are 392 meetings but only 37 employees contributed meetings, so it is dividing 392/37 which is the number 11 but I was expecting / hoping it would divide by 40 which would give me 10 instead, which would be the total number of unqiue employee ids for that time period even though 3 of those employees didnt contribute a record of that type. Not sure how to modify my equation though to handle that.
      • jweave98's avatar
        jweave98
        Frequent Visitor
        7 years ago

        Here is a better screenshot without the report filtered down to the employee level to see the totals from the top. 

  • RaduH's avatar
    RaduH
    New Member
    6 years ago

    Hi there, I think there are 2 issues here:

    a) Formula to be clearer defined - Total average cannot equal the sum of individual averages, so using DivisionCount function will probably not work.

    b) Create a clear Employee ID table which has unique values (can take a payroll query, refference it, Drill Down and extract only the Employee ID, department and name). This will help you long-term anyway.

    c) What do you try to show?

    I would reccomend to get clear on what you try to achieve:

    do you want to see the total company average vs. individual average? Then use AVERAGE as a measure or even as a calculated column - but then the with a different visualization than a stacked graph?

    Think of a different Matrix table too.

     

    I hope this helps.