Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating Grouped Average of RowCount with FILTER

Hi all,   I am trying to create a measure to show the Average number of Deals that Salespeople have made against the Number of Deals they have made. This measure will be used as the Target in a Gau...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  Anonymous ,

     

    I'm not sure whether I have understood correctly.

    Create a measure as below:

    Measure = 
var _id=CALCULATE(MAX('systemusers'[userid]),FILTER(ALL(systemusers),'systemusers'[yomifullname]=SELECTEDVALUE(systemusers[yomifullname])))
var _count=CALCULATE(COUNTROWS('wt_enquiries'),FILTER(ALL('wt_enquiries'),'wt_enquiries'[userid]=_id&&'wt_enquiries'[wt_masterstatus]="206330005"))
var _totalcount=CALCULATE(COUNTROWS('wt_enquiries'),FILTER(ALL('wt_enquiries'),'wt_enquiries'[wt_masterstatus]="206330005"))
Return
DIVIDE(_count,_totalcount)

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi Kelly,

 

Attached is a sample of the data. I can't add the expected output because I can't get the formula to work. I am trying to get a target value that would be the average number of rows with the code "20663005" grouped by the user.

 

I'm aware the number in the above code is not the same as the one mentioned in the initial post, I have changed this but I am still getting the same result.

 

File can be found here: https://easyupload.io/dxkxoq