Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
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
Hello!
Yes I prepared an excel file with fake data, however I cannot see how to upload it here. Am I missing that?
John
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!
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 |
@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.
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
@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.
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...
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 |
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 |
@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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |