Creating Grouped Average of RowCount with FILTER

[Anonymous]

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 Gauge. The number deals is the count of rows with the code "206330005" which is "Deal Signed".  

The concept is they will be able to select their name from a slicer, as well as the region they work in and the time period they want to look at. They will then be able to track their performance vs the average performance in the region/time period.

However, I am struggling to create a DAX forumla to acheive this, I've tried the below formula and there's no error, but the the slicer for salesperson still effects the average. I have also created a measure to filter the below forumla by ALL sales people which still didn't work.

Average Grouped Completed Deals = 
VAR CompletedDeals = CALCULATE(
                            COUNTROWS(wt_enquiries),
                            wt_enquiries[wt_masterstatus]IN{"206330005"})

RETURN

AVERAGEX( 
        SUMMARIZE( systemusers, systemusers[yomifullname] , "RowCount", 
            CompletedDeals), [RowCount] )

Can anyone help?

[v-kelly-msft · Accepted solution]

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]

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

[v-kelly-msft]

Hi @Anonymous,

Could you pls provide some sample data with expected output for test?

Remember to remove the confidential information.

Best Regards,
Kelly

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