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

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.

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
9 years ago

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.