Forum Discussion
BIsteht
Helper III
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] = Sal...
- @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
BIstehtHelper III
Helper IIIMFelix 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
Super User
Super User@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]
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]