Forum Discussion

bflaviu's avatar
bflaviu
Frequent Visitor
9 years ago
Solved

Filter COUNTROWS Where Values Equal Values from Another Table

I am tracking KPIs for some sales reps, so I have a table of all the sales calls they made witih a column that shows what sales method they used (Cold Calls, Meeting, Etc.)  Each sales rep has chosen a single type of method, and and goal number of calls for that method they must make a week, so in another table, I have the Sales Rep, the Method Type, and the Goal Number.  I want to create a measure, where I count only the sales calls from the Sales Calls table that match the chosen type for that sales rep as defined in the KPI table.

 

 

So I want to get a count from Sales Calls where if I chose the context of "John", it only counts the rows where the 'Sales Calls'[Method] = 'KPI Settings'[KPI Method] for John (so count all the "A" calls for Jonh, "A" calls for Bob, and "B" calls for Tim).

 

The tables are related by Sales Rep.

 

Thanks in advance! 

 

Flaviu

  • How about these 2 Measures?

     

    Total Calls = COUNTROWS(SalesCalls)
    
    KPI Method Calls =
    CALCULATE (
        [Total Calls],
        FILTER (
            SalesCalls,
            SalesCalls[Method] = RELATED ( 'KPI Settings'[KPI Method] )
        )
    )

    EDIT: I used M1 (A) , M2 (B), M3 (C)

2 Replies

  • Sean's avatar
    Sean
    Community Champion

    How about these 2 Measures?

     

    Total Calls = COUNTROWS(SalesCalls)
    
    KPI Method Calls =
    CALCULATE (
        [Total Calls],
        FILTER (
            SalesCalls,
            SalesCalls[Method] = RELATED ( 'KPI Settings'[KPI Method] )
        )
    )

    EDIT: I used M1 (A) , M2 (B), M3 (C)

    • bflaviu's avatar
      bflaviu
      Frequent Visitor

      That worked perfectly!  Thanks!  I was really confused about how the RELATED function worked.  I was getting lost trying to use LOOKUPVALUE function.