Forum Discussion

BIsteht's avatar
BIsteht
Icon for Helper III rankHelper III
9 years ago
Solved

Total 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...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago
    @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's avatar
BIsteht
Icon for Helper III rankHelper III
9 years ago

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's avatar
MFelix
Icon for Super User rankSuper User
9 years ago
@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's avatar
    BIsteht
    Icon for Helper III rankHelper III
    9 years ago

    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's avatar
      MFelix
      Icon for Super User rankSuper User
      9 years ago
      @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]