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.

MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 years ago

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

 

 

 

  • 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."