Create A Custom Metric on Power BI for a call center

[anshul1123]

Hi Everyone,

I have two files linked with Power Bi, One Excel File where i have all the errors/defect call info of all agents and another file where i have total call info of the agents. The first file has a cost of error included against each call. Now here is what i am trying to achieve:

• count of total errors divided by total calls for the same period of time for each individual 
• count of total errors divided by total calls for the same period of time for the total team  
• cost of error divided by total revenue generated for the same period of time for each individual 
• cost of error divided by total revenue generated for the same period of time for the total team  
• Basis Point calculations on the above 

Please help.

Thank you!!

[Anonymous]

Hi @anshul1123 ,

You can create some measures to get them by using the function DISTINCTCOUNT,SUM,DATESBETWEEN, DIVIDE and so on... Just for example:

Percentage for per individual=
DIVIDE (
    CALCULATE (
        DISTINCTCOUNT ( 'ERROR CALLS'[ERROR_ID] ),
        FILTER (
            'ERROR CALLS',
            'ERROR CALLS'[EMPLOYEE] = MAX ( 'ERROR CALLS'[EMPLOYEE] )
                && DATESBETWEEN ( 'ERROR CALLS', [start date], [end date] )
        )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'TOTAL CALLS'[CALL_ID] ),
        FILTER (
            'TOTAL CALLS',
            'TOTAL CALLS'[EMPLOYEE] = MAX ( 'TOTAL CALLS'[EMPLOYEE] )
                && DATESBETWEEN ( 'TOTAL CALLS', [start date], [end date] )
        )
    )
)

If you don't know how to use these functions, then please provide some sample data in this two files and I will provide the formulas which is applicable for your scenario. Thank you.

Best Regards

Rena

[anshul1123]

Hello Ying, 

Firstly, thank you for that implimentation. I am good sharing data however i will need a more secure way of showing that to you as this is internal company info.

If there is a possibility for a one on one conversation, do let me know.

In the meanwhile - I am going to try this out and see where I land as i am a novice with coding and data visualisation at this point.

Again - Really appreciate the help! 

Thanks!

Anshul 

[Anonymous]

Hi @anshul1123 ,

You can mock up some data and share with me, so that I can know which tables you have and what the structure looks like (just as shown in the screenshot below).

Best Regards

Rena

[anshul1123]

Here is some sample data i copied on to a google sheet :

• Error sheet:
  • Link to Sheet 
• Total Sample Sheet 
  • Sheet 2 of same sheet 

Thanks again 

[Anonymous]

Hi @anshul1123 ,

Thank you for the information. Could you please answer my following questions in order to provide you with a suitable formulas:
1. How to judge whether it is error (when Error = "Yes" or Error Category = "error")?
2. How to know who is in the same team?
3. Does cost means the time spent on error? It refer the "hour_table" column in total table?
Could you provide the calculation logic with a text formula?

Best Regards

Rena

[anshul1123]

Hi Rena,

Were you able to view the data?

Thanks much!

Anshul 

[anshul1123]

Hello Rena,

• Error = Error Category column to say "Error"
• The entire team is going to same manager so it is the same team 
• Cost would mean the amount of the product (Ord_AMT)
•  The calculation would mean 2 things :
  1.  count of Error (from error sheet)/count of total orders done( from 2nd sheet) - for same person 
  2. count of Error(from error sheet) /count of Total Errors ("Error Category - No Error+Error") from the same error sheet  - for same person 

Thanks again!

Anshul 

[Anonymous]

Hi @anshul1123 ,

I just create a sample report file, you can get it from this link.

Best Regards

Rena

[anshul1123]

Hi Rena,

This still seems to be inaccurate when i replicate on the original Data. 

The percentages on the values on my end when checked on excel seem accurate, however when replicated through the formula on Power BI they still come out to be inaccurate. 

I am sending you the data via PM to check .

Thanks again , Cant say it enough @Anonymous  🙂 !

Regards,

Anshul