cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
phteven79 Frequent Visitor
Frequent Visitor

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

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 >_<

7 REPLIES 7
GabrielSantos Regular Visitor
Regular Visitor

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

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

phteven79 Frequent Visitor
Frequent Visitor

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

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

GabrielSantos Regular Visitor
Regular Visitor

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

Not a stupid question Smiley Happy

 

Try uploading on dropbox and posting the link here.

phteven79 Frequent Visitor
Frequent Visitor

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

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 Regular Visitor
Regular Visitor

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

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))
Highlighted
GabrielSantos Regular Visitor
Regular Visitor

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

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 Frequent Visitor
Frequent Visitor

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

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. Smiley Very Happy