Re: Category Comparison Graph

JCovelli · ‎10-11-2018

Hey Everyone,

I would like to compare our categories of Call Intent Types by day, looking at one particular category’s calls versus the total calls. I would also like to do this comparison by Division. I have created a FAKE DATA set that illustrates what I mean. Any help?

In the REAL data, that spans a full year, and over 30 categories, we are looking to see what categories experience abnormal spikes and on what days, or times of the year. At first, I did it in a crude way: I created two pivot tables: one pivot table with the total call intents by day, and then a second pivot table and slicer so I could select a specific category (or several) and compare to the day. A third section would do a ratio of the other two by date.

One problem I had was that some days do not have any calls for a particular category, so the table-to-table comparison would no longer line up to the day… for graphing purposes, etc.

Thanks!

John

v-piga-msft · ‎10-12-2018

Hi @JCovelli,

If it is convenient, could you share some data sample and your desired output so that we could help further on it?

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

JCovelli · ‎10-12-2018

Hello!

Yes I prepared an excel file with fake data, however I cannot see how to upload it here. Am I missing that?

John

JCovelli · ‎10-12-2018

So, I have just now discovered the "New Quick Measure" feature and created a line graph that shows the performance by date of one category AND the total of all categories- two lines....

If I could CONVERT this to show the % To Total- ONE LINE-, then I could do the Divisional analysis... I think I am getting closer!

JCovelli · ‎10-12-2018

		Number Of calls By date
	Category	1/1/2018	1/2/2018	1/3/2018	1/4/2018	1/5/2018	1/6/2018	1/7/2018	1/8/2018	1/9/2018	1/10/2018
	Claims	1237	1021	398	570	995			22595	20922	20225
Division One		247	204	80	114	199	0	0	4519	4184	4045
Division Two		433	357	139	200	348	0	0	7908	7323	7079
Division Three		557	459	179	257	448	0	0	10168	9415	9101
	Billing	2633	2313	1053	1541	1776			2473	2460	2458
Division One		527	463	211	308	355	0	0	495	492	492
Division Two		922	810	369	539	622	0	0	866	861	860
Division Three		1185	1041	474	693	799	0	0	1113	1107	1106
	Complaints	2722	2495		1738	1984	166	72	2490	2021	1986
Division One		544	499	0	348	397	33	14	498	404	397
Division Two		953	873	0	608	694	58	25	872	707	695
Division Three		1225	1123	0	782	893	75	32	1121	909	894
	Address Change	2163	1521			2597	1237	1021	398		1541
Division One		433	304	0	0	519	247	204	80	0	308
Division Two		757	532	0	0	909	433	357	139	0	539
Division Three		973	684	0	0	1169	557	459	179	0	693
	Total Call Intents	15347	13179	2902	7698	12107	1569	1165	55514	50806	50879
Division One		3069	2636	580	1540	2421	314	233	11103	10161	10176
Division Two		5371	4613	1016	2694	4237	549	408	19430	17782	17808
Division Three		6906	5931	1306	3464	5448	706	524	24981	22863	22896

parry2k · ‎10-12-2018

@JCovelli you can share your sample data file thru onedrive/dropbox/google drive and also here is link to an article on best practice to get answer to your question quickly.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

JCovelli · ‎10-12-2018

Thank you @parry2k! I just pasted into a post...at work, unfortunately I do not have access to the other filesharing mechanisms you mentioned.

John

parry2k · ‎10-12-2018

@JCovelli is this how your raw data looks like? Please confirm. It need to unpivoted and than everything will work great.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

JCovelli · ‎10-12-2018

I had actually mocked up a version of the aggregated data to illustrate. But I see now you want a raw set. I tried to mock up a set of raw data, but now I am getting a meesage when I psate it that I am exceeding 20,000 characters...

JCovelli · ‎10-12-2018

Date	Division	Category
1/1/2018	Division One	Address Change
1/2/2018	Division Two	Address Change
1/3/2018	Division Two	Address Change
1/4/2018	Division Three	Address Change
1/5/2018	Division One	Billing
1/6/2018	Division Two	Billing
1/7/2018	Division Three	Billing
1/8/2018	Division One	Billing
1/9/2018	Division Two	Claims
1/10/2018	Division Two	Claims
1/1/2018	Division Three	Claims
1/2/2018	Division One	Claims
1/3/2018	Division Two	Complaints
1/4/2018	Division Two	Complaints
1/5/2018	Division Three	Complaints
1/6/2018	Division One	Complaints
1/7/2018	Division Two	Address Change
1/8/2018	Division Two	Address Change
1/9/2018	Division Three	Address Change
1/10/2018	Division One	Address Change

JCovelli · ‎10-12-2018

1/3/2018	Division Two	Billing
1/4/2018	Division One	Billing
1/5/2018	Division Two	Billing
1/6/2018	Division One	Billing
1/7/2018	Division Two	Billing
1/8/2018	Division Three	Billing
1/9/2018	Division One	Billing
1/10/2018	Division Two	Billing
1/1/2018	Division Three	Billing
1/2/2018	Division One	Billing
1/3/2018	Division Two	Billing
1/4/2018	Division Three	Billing
1/5/2018	Division One	Billing
1/6/2018	Division Two	Billing
1/7/2018	Division Three	Billing
1/8/2018	Division One	Billing
1/9/2018	Division Two	Claims
1/10/2018	Division Three	Claims
1/1/2018	Division Two	Claims
1/2/2018	Division Three	Claims
1/3/2018	Division One	Claims
1/4/2018	Division Two	Claims
1/5/2018	Division Three	Claims
1/6/2018	Division One	Claims
1/7/2018	Division Two	Claims
1/8/2018	Division Three	Claims
1/9/2018	Division Two	Claims
1/10/2018	Division Three	Claims

parry2k · ‎10-15-2018

@JCovelli see attached, I guess tht will help. You can slicer the graph by category or division, and it will compare against total calls.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

JCovelli · ‎10-16-2018

Thanks so much for sending a solution! I am excited to open it- I am unfortunately constrained here at work with downloading the newest version of Power BI Desktop, and your version must be newer than mine, because I am unable to open the file. I will try to download the newest version at home so I can open your file. Thanks for your help!

John