Percentage calculation

[jeevan_mehra]

Hi,

I have half hourly intervalwise raw data available on which when I use pivot in excel and get #DIV error, I replace those with '0' which make my pivot look presentable. However, when I use the same data in power BI and add a new column as abandoned % in data view where i use total abandoned/total calls offered and add that formulated column in visuals, I get the error as "NaN". Now, I am not able to replace this with '0'. My question is, how do I do that? 

Looking for instant help..thanks

[v-shex-msft]

Hi @jeevan_mehra,

Can you provide the measure formulas which you mentioned? I'm not very clear these formula.

Regards,

Xiaoxin Sheng

[jeevan_mehra]

Hi,

For calculating SL% I have used measure - DIVIDE (calls answered in 20 sec, net calls offered(Total calls offered-calls aban in 10 sec))

For calculating Aban% I have used measure - DIVIDE (total calls abandoned, total calls offered)

[Sean]

@jeevan_mehra

Use the DIVIDE function

https://msdn.microsoft.com/en-us/library/jj677276.aspx

[jeevan_mehra]

Thanks sean for your help. But, still not able to get the perfect result. Actually, I am calculating SL%, Aban% and Ans% based on the raw data. However, the result varies big time in power bi as compared to excel. For eg. SL% for 1st day of the month in excel comes to 89.3% and comes as 97.44% in power bi. Not sure, what to do next??

[v-shex-msft]

Hi @jeevan_mehra,

Can you please share some detail content of your issue? It is hard to reproduce your issue from your description.

Regards,

Xiaoxin Sheng

[jeevan_mehra]

Hi,

I am not sure how to attach my data here. Can you guide me on that?

[v-shex-msft]

Hi @jeevan_mehra,

You can upload to your file to 1dv and share the link here.

Regards,

XIaoxin Sheng

[jeevan_mehra]

Hi,

I am actually new to this environment. Can you explain me what 1dv is?

[v-shex-msft]

Hi @jeevan_mehra,

1dv means the "Microsoft OneDrive".

Regards,

Xiaoxin Sheng

[jeevan_mehra]

Hi,

I am not able to connect to Microsoft OneDrive as of now. However, I am pasting my data below. Let me know if it helps..

Date	Time	Total Calls Offered	Total Calls Answered	Calls Answered After 20 Sec	Calls Answered within 20 Sec	Abandoned Calls	Abandoned <10 Sec	SpeedOfAnswer	Talk_time	ACW_Time	Hold_time	Handled calls	LOB
01-Feb-17	0:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	0:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	1:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	1:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	2:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	2:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	3:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	3:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	4:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	4:30	1	1	0	1	0	0	3	199	3	0	1	Prepaid 12345 Gold
01-Feb-17	5:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	5:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	6:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	6:30	1	1	0	1	0	0	4	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	7:00	0	0	0	0	0	0	0	324	7	0	1	Prepaid 12345 Gold
01-Feb-17	7:30	2	2	1	1	0	0	37	260	10	0	2	Prepaid 12345 Gold
01-Feb-17	8:00	2	2	0	2	0	0	8	153	7	0	1	Prepaid 12345 Gold
01-Feb-17	8:30	1	1	0	1	0	0	0	705	10	41	2	Prepaid 12345 Gold
01-Feb-17	9:00	1	1	0	1	0	0	4	87	7	0	1	Prepaid 12345 Gold
01-Feb-17	9:30	5	5	1	4	0	0	47	580	23	33	5	Prepaid 12345 Gold
01-Feb-17	10:00	1	1	0	1	0	0	4	51	3	0	1	Prepaid 12345 Gold
01-Feb-17	10:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	11:00	1	1	0	1	0	0	21	131	7	0	1	Prepaid 12345 Gold
01-Feb-17	11:30	10	10	0	10	0	0	30	769	54	0	10	Prepaid 12345 Gold
01-Feb-17	12:00	8	8	0	8	0	0	27	183	40	0	8	Prepaid 12345 Gold
01-Feb-17	12:30	1	1	0	1	0	0	4	85	7	0	1	Prepaid 12345 Gold
01-Feb-17	13:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	13:30	3	3	0	3	0	0	4	517	17	46	3	Prepaid 12345 Gold
01-Feb-17	14:00	3	3	0	3	0	0	8	547	17	0	3	Prepaid 12345 Gold
01-Feb-17	14:30	2	2	0	2	0	0	8	300	10	0	2	Prepaid 12345 Gold
01-Feb-17	15:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	15:30	1	1	0	1	0	0	3	246	3	62	1	Prepaid 12345 Gold
01-Feb-17	16:00	1	1	0	1	0	0	1	100	3	0	1	Prepaid 12345 Gold
01-Feb-17	16:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	17:00	3	2	0	2	1	0	6	280	3	0	1	Prepaid 12345 Gold
01-Feb-17	17:30	0	0	0	0	0	0	0	107	3	0	1	Prepaid 12345 Gold
01-Feb-17	18:00	2	2	0	2	0	0	8	258	6	0	2	Prepaid 12345 Gold
01-Feb-17	18:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	19:00	7	7	1	6	0	0	44	1282	15	0	5	Prepaid 12345 Gold
01-Feb-17	19:30	5	5	0	5	0	0	11	1262	21	0	7	Prepaid 12345 Gold
01-Feb-17	20:00	1	1	0	1	0	0	4	78	3	0	1	Prepaid 12345 Gold
01-Feb-17	20:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	21:00	3	3	1	2	0	0	38	1003	9	0	3	Prepaid 12345 Gold
01-Feb-17	21:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	22:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	22:30	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	23:00	0	0	0	0	0	0	0	0	0	0	0	Prepaid 12345 Gold
01-Feb-17	23:30	2	1	1	0	1	0	76	60	3	0	1	Prepaid 12345 Gold

[WP]

Hi jeevan_mehra,
Before you can use your data you have to prepare it and put it in a datamodel with calendar table.
I would before starting to calculate, add an index column as a primary key and afterwards unpivot all columns except index, date, time and the one with prepaid gold in.
Afterwards you can create measures using this data. If you don't know how to start, I would suggest you take the edx powerbi course which is great introduction to the tool and it does a great job explaining the tool

[jeevan_mehra]

Hi,

I have a table with complete month data and it has some columns like total calls offered, answered, abandoned etc. Now, if I am trying to add a column like aban% where I am dividing abandoned by offered and trying to reflect it in my chart I am not getting the result which I am expecting, like it is giving me infinity error for some dates under the aban% column. Now, my main concern is how do I get rid of it. I know I can do this in excel but not sure how to do it in power bi. So, I am really looking for some help here...