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 = DISTI...
Data Modeling
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.