Struggling to get the Top 10 performing items with an extra filter in place

[phteven79]

I have data for incoming telephone numbers to an insurance call centre.

I need to see the top 10 phone numbers called based on the call to quote rate on numbers that have an average daily calls received of 40 or more.

All numbers that have 100% call to quote rate, or close to 100%, receive a tiny amount of calls per day on average from 1-4, most if not all of which are converted into quotes. These will be looked at for a separate piece of work. They're not relevant for this.

So far;

I've created measures in Power BI to; calculate the average number of calls that each number receives per day and calculates the conversion rate of answered calls into actual quotes.

Here's the measure for the Avg Calls Ans Per Day (you can see it's made up of other measures);

Avg Calls Ans Per Day = DIVIDE([TotalAnswered],[Number of Days])

Here's the measure for Call to Quote rates (you can see it subtracts a certain type of call);

Call to Quote (exc Trans) = 
CALCULATE (
           DIVIDE ( 
                   SUM(CTIPublication[Quotes]),
                   SUM(CTIPublication[Answered]) - SUM(CTIPublication[Transferred])
                   )
           )

If I add a TopN filter to the phone number visual level filter on the table as top 10 based on the call to quote measure AND then add a filter to the Avg Calls Ans Per Day of greater than or equal to 40 this won't work because of course the Top 10 performing numbers don't receive that many calls per day on average and the measure applies to the entire data set.

If try to adjust the Call to Quote measure instead, to the following;

Call to Quote (exc Trans) = 
CALCULATE (
           DIVIDE (
                  SUM(CTIPublication[Quotes]),
                  SUM(CTIPublication[Answered]) - SUM(CTIPublication[Transferred])
                  ),
      FILTER ( 
              CTIPublication, [Avg Calls Ans Per Day] >= 40
             )
          )

This makes the results look more reasonable at first glance until I spot 3 rows in the top 10 that have average calls per day of less than 40 (indicated with <--- )

Phone Number | Avg Calls Ans Per Day | Call to Quote (exc Trans)
-------------|-----------------------|--------------------------
08005426717  | 34.8     <---         |  54.41%
1800818751   | 46.8                  |  29.15%
08006300032  | 20.7     <---         |  27.37%
08008047952  | 59.2                  |  27.23%
08001444645  | 76.5                  |  26.81%
08002985500  | 37.8     <---         |  20.83%
01612225250  | 43.1                  |  19.89%
08002800743  | 45.1                  |  4.89%
1800298551   | 64.7                  |  3.81%
08009776898  | 166.3                 |  2.95%

So it feels like I don't really understand how the FILTER function works, or ranking maybe? I've about reached the end of my rope trying to get this to work.

For clarification, we don't want to give the report users an option of TopN of their choice via a slicer, I need it fixed on top 10 call to quote rates filtering out phone numbers that receive less than 40 calls per day on average.

If that makes sense, please help >_<

[GabrielSantos]

Can you share your .pbix? It will be helpful to see the tables involved.

[phteven79]

Sure.... erm, stupid question, how do I do that? 

[GabrielSantos]

Not a stupid question 🙂

Try uploading on dropbox and posting the link here.

[phteven79]

We're not allowed to use Dropbox. I've done it in OneDrive if that's accessible to you?

https://1drv.ms/u/s!AiiJ7Vh0iM6m2n7oOr1jJjYfq59z

[GabrielSantos]

I'm going to write this in two parts.

First, to obtain the top 10 publications with greater than 40 average calls per day, define the following measures:

QuotesSumX := SUMX( CTIPublication,
                                   CALCULATE( SUM( CTIPublication[Quotes])))

AnsweredLessTransfers := SUMX( CTIPublication,
                                                 CALCULATE( SUM ( CTIPublication[Answered])))
-
                                          SUMX( CTIPublication,
                                                 CALCULATE ( SUM ( CTIPublication[Transferred])))

Top10CallToQuote := CALCULATE ( DIVIDE ( [QuotesSumX] , [AnsLessTransQuotes] ) ,
TOPN(10 ,FILTER (
       SUMMARIZE( CTIPublication,
       CTIPublication[Publication],
          "AvgCalls",AVERAGEX( CTIPublication,
                              CALCULATE ( SUM ( CTIPublication[Answered]))),
          "Quotes", SUMX (CTIPublication,
                              CALCULATE ( SUM ( CTIPublication[Quotes]))),
          "AnsLessTrans", SUMX ( CTIPublication, CALCULATE( SUM (CTIPublication[Answered]))) - SUMX ( CTIPublication,                                                           CALCULATE ( SUM (CTIPublication[Transferred]))))
                                  ,[AvgCalls] > 40), DIVIDE ( [Quotes] , [AnsLessTrans] ), DESC))

[GabrielSantos]

IF([CallToQuoteRatio],
CALCULATE( [AverageCallsPerDayAvgX],
            TOPN(10,FILTER(
SUMMARIZE(CTIPublication,
             CTIPublication[Publication],
             "AvgCalls",AVERAGEX(CTIPublication,
                           CALCULATE(SUM(CTIPublication[Answered])))
             , "Quotes", SUMX(CTIPublication,
                               CALCULATE(SUM(CTIPublication[Quotes]))),
              "AnsLessTrans", SUMX(CTIPublication,CALCULATE(SUM(CTIPublication[Answered]))) - SUMX(CTIPublication, CALCULATE(SUM(CTIPublication[Transferred]))))
               ,[AvgCalls] > 40), DIVIDE([Quotes],[AnsLessTrans]), DESC)))

This is a trickier one. I had to use the IF statement to evaluate the [CallToQuoteRatio] for the presence of a value because one of CNIC Letterheads - 08002985533 had on average > 40 calls per day, but all null quotes. I might suggest entering zeros instead of blanks for Quotes column to properly calculate your division.

[phteven79]

Sorry it's taken me so long to reply, we we're out on a training course yesterday.

Thank you so much for all the work you did on that, I really appreciate it.

It gets me one step closer but what I'm seeing from it is a top 10 based on the highest number of average calls per day to each publication and in that respect it works perfectly. 

I need to give the top 10 publications based on call to quote ratio but where the average calls per day are only taken into account if they are 40 or higher for that publication.

I must admit, this has shown me that I need to learn more DAX because I don't understand a lot of what you've created to be able to reuse it! It's a good learning source for me though.