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!

v-kelly-msft's avatar
v-kelly-msft
Icon for Community Support rankCommunity Support
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!