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
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

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

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's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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.