Total row count based on values in two different columns and filter from related table

[BIsteht]

Please see the graphic below for an explanation of what I am trying to accomplish. I need a solution that allows me to count the number of rows for each employee in Table1 where Table2[JobType] = SalesMgr. The challenge is that I need a count of the rows where that SalesMgr from Table2 is either the SalesMgr OR Recruiter on Table1.

Note that an employee cannot be both the SalesMgr AND Recruiter on a sale (Table1). Can be one or neither.

[MFelix]

Hi @BIsteht,

Takin into account that the ID only refers to sales managers or Recruiter You have to follow this steps:

1 - In query Mode unpivot columns Table1[SalesMGR] and Table1[Recruiter].

     The output will be a new table with 4 columns:

     Sales MGR

     Recruiter

     Attribute - Corresponds to JobType

     Value - ID corresponding to Table 2

2 - Do relations ship between the two table  by field Table1[Value] and Table2[ID]

 3 - Select columns below to do your visual and add a slicer or filter in the visual with the Table1[Attribute] :

 Table2[Name]

 Table1[SalesMGR] as Count

The filter by attribute will allow you to have the calculation made by the sales manager or Recruiter ID in the table 1 if you do it By JobType the calculation for Recruiter will be incopmplete since Peter in table2 is a Salesmanager and not a recruiter.

We can also use some measure but in order to simplify your data I believe this is the easist way because since you have two columns with the same relationship you have to use several DAX formulas they are possible but will complicate your needs, again in my opinion.

Regards

MFelix

[BIsteht]

Thanks @MFelix. This solution duplicates the dataset and double the record count, no?  I don't think I can pivot these and keep other required elements of the data model intact (aggregations, averages, etc.). Sorry about that. I was trying to simplify my presentation but clearly left out important details.

[MFelix]

HI @BIsteht,

I didn't know that, then in my opinion the best way is to related both columns and use the USERRELATIONSHIP formula (sorry @Greg_Deckler  😄 😄 don't believe that doubling the tables is better) if you want try the solution below.

1 - Two relationships:

      a) Table1[SalesMGRID] -> Table2 [ID] = active relationship

      b) Table1[RecruiterID]   -> Table2[ID] = inactive relationship

2 - Make this measure

Count of sales = 
		var salesMGR = CALCULATE(COUNT(Table1[SalesMGR]))
		var Recruiter = CALCULATE(COUNT(Table1[SalesMGR]); USERELATIONSHIP(Table2[ID];Table1[RecruiterID]))
		Return
		salesMGR+Recruiter

Using Variables to calculate each column count sales ID and recruiter ID see that the var Recruiter uses the userrelationship that is the inactive relationship we did in step 1

The two var can be made as single measures and used in the same table to make a count by salesmgr and the other by recruiter in the same visual.

3 - Insert slicer with field Table2[JobType] or add this field as a filter in the table of step 4.

4 - Make you table with following fields and disable totals if needed:

    a) Table 2[Name]

    b) Count of Sales

This table should be filtered by job type if you want to have the result by JobType,.

As you can see the result is what you need without the need of duplicate data.

Hope this helps but option given by @Greg_Deckler is also good.

Regards

MFelix

[BIsteht]

@MFelix great solution! Didn't need to duplicate the data. One more question - how would I do something simliar but an average on a column. I updated your formula to try and accomplish this but you'll see that one row in my table does not return results for BR1 (formula used below). That SalesMgr only has sales where he was the Recruiter, none as SalesMgr. The other Sales Mgrs sales were all when acting as a SalesMgr, and therefore results are being returned. At least I think that's what's happening here:

Avg BR1 = 
		var SalesMgr = CALCULATE(average('Table'[Bill Rate]))
		var Recruiter = CALCULATE(average('Table1'[BillRate]), 
                USERELATIONSHIP('Table2'[ID],'Table1'[RecuiterID]))
		Return
		AVERAGE('Table1'[Bill Rate]

[MFelix · Accepted solution]

@Blsteht,

The previous measure is for a count so is compose by two variables that we sum and the result is.correct, of.you sum two averages the result will not be the average. You should do 4 variables

var SalesMgr = CALCULATE(sum('Table'[Bill Rate]))
var Recruiter = CALCULATE(sum('Table1'[BillRate]),
USERELATIONSHIP('Table2'[ID],'Table1'[RecuiterID]))
var CountSalesMgr = CALCULATE(count('Table'[Bill Rate]))
var countRecruiter = CALCULATE(count('Table1'[BillRate]),
USERELATIONSHIP('Table2'[ID],'Table1'[RecuiterID]))

Now use this to the return result:
(Salesmgr + recruiter) / (countsalesmgr + countrecruiter).

I assume.you want simple average. Should work.

Another thing that I noticed is that you return the average of table1 sales that's why you are only getting results on sales managers since is the active relationship in your return you nust use the variables to make the calculations and not the columns or the results will be parcial.

Regards
Mfelix

[BIsteht]

@MFelix Can you explain the last portion of your response? This may be why I'm still not seeing what I am hoping for on the Average columns.

"Another thing that I noticed is that you return the average of table1 sales that's why you are only getting results on sales managers since is the active relationship in your return you nust use the variables to make the calculations and not the columns or the results will be parcial."

[MFelix]

@Blsteth,

When you wrigth a measure you can do it directly and have a calculation example:Sum(table1[Bill Rate]) or using an equation format with variables and calculations over columns and your result will be the formula you present after the word RETURN.

In your case you wrote


Avg BR1 =
var SalesMgr = CALCULATE(average('Table'[Bill Rate]))
var Recruiter = CALCULATE(average('Table1'[BillRate]),
USERELATIONSHIP('Table2'[ID],'Table1'[RecuiterID]))
Return
AVERAGE('Table1'[Bill Rate]

Although you are cslculating the variables the result after RETURN is the AVERAGE over a single column so your result will not be impacted by the previous variables.

Regards,
MFelix


Avg BR1 =
var SalesMgr = CALCULATE(average('Table'[Bill Rate]))
var Recruiter = CALCULATE(average('Table1'[BillRate]),
USERELATIONSHIP('Table2'[ID],'Table1'[RecuiterID]))
Return
AVERAGE('Table1'[Bill Rate]

[Greg_Deckler]

OK, I solved this I believe. Here is what you need:

• Your Table 1
• Duplicate of your Table 1
• Table 2
• Duplicate of Table 2
• Triplicate of Table 2

Relate Table 2 to Table 1 on SalesMgrID. Relate Duplicate of Table 2 to Duplicate of Table 1 on RecruiterID. Relate both Table 2 and Duplicate of Table 2 to Triplicate of Table 2 on SalesMgrID and RecruiterID respectively. Create the following measures:

In Table 1:

# of Sales = CALCULATE(COUNT([SalesMgrID]),RELATEDTABLE(SalesManagers))

In Duplicate of Table 1

# of Recruiter Sales = CALCULATE(COUNT([RecruiterID]),RELATEDTABLE(Recruiters))

In Triplicate of Table 2:

# of Total Sales = [# of Recruiter Sales] + [# of Sales]

Now, in your visual, place the Name from the Triplicate of Table 2 and your measure # of Total Sales. If you only want sales managers, filter your visual to only include sales managers.

[Greg_Deckler]

I think you should split your Table 2 into 2 tables, one for Sales Mgr's and one for Recruiters. Then you should be able to have multiple relationships to Table 1 and simply do a count of related records and then sum those counts.

[BIsteht]

Thanks for the quick repsonse @Greg_Deckler. I actually do have 2 copies of Table2 exactly as you state but can't figure out how to make that work in order to aggregate the total count into a Table visual. Which table would I pull the SalesMgr name from in my visual?

[Greg_Deckler]

I would pull the Sales Mgr from your Sales Mgr table in your visual. Technically, you wouldn't even need the name of the recruiter or sales manager in Table 1. You would then have a measure that should look something along the lines of:

# of Sales = CALCULATE(COUNT([SalesMgrID]),RELATEDTABLE(SalesManagers)) + CALCULATE(COUNT([SalesMgrID]),RELATEDTABLE(Recruiters))

This isn't the final answer but should put you along the right line. I'll play with it some more if I find some free time.