Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Struggling to get the Top 10 performing items ...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

09-20-2017
07:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-20-2017
08:58 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-20-2017
09:03 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-20-2017
09:18 AM

Not a stupid question 🙂

Try uploading on dropbox and posting the link here.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-20-2017
09:23 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-20-2017
12:42 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-20-2017
01:06 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

09-22-2017
08:15 AM

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.