Forum Discussion
Helper IIITotal row count based on values in two different columns and filter from related table
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.
- @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
11 Replies
- Greg_Deckler
Community Champion
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.
- MFelix
Super User
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 :D :D 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
