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.
Hi, I am having trouble trying to visualize the effects of the notification on Sales.
I have three tables of content:
One which describes the Notification and have the ID and the Dispatch time
One which has the NotificationID, UserID and Dispatch Status (this is if the user received or not)
One which I have Sales information including UserID and time
I would like to see the impact of number of sales and amount spent by the diference between the dispatch time and sale time. Also I would like to compare the sales between users that received the Notification or Not. Like how many users that received bought something and how many that did not received bought in the same time period.
For that I would like to Filter the sales by diferent diferences in time.
I am not an expert in Power Bi, actually I am pretty new to it. So I don't actually know many power query tools
Thank you
Solved! Go to Solution.
Nice.
A couple of things. I suspect that these sample data are simplified, perhaps to much. As long as each customer only has 1 notification, the relationships are 1-to-many. But once you have a customer with more than 1 notification the relationships between the tables will be many-to-many. Many-to-many relationships are much more complex to handle than 1-to-many.
You wrote that you want to analyse on the difference in time between dispatch and sales. How should the difference be calculated when there are more than 1 sale?
You were also looking for the difference between sales to customers who received notification to those that don't. But your sample sales data does not contain any customers who did not reveice notification.
What you could do is to do some calculations on your sample data in Excel to describe you desired output.
If you are having problem sharing the data directly in the forum, you can upload the data to dropbox/onedrive/other, and share the link
Hi @bcosta98,
your question is well written, but it will be much easier to help you if you add some sample data, or a sample report.
Cheers,
Sturla
Hi @sturlaws
This are the tables
And the relations goes as follows:
Notification 1:* Notification_User
Notification_User *:1 User
User 1:* Sales
Could you attach your sample data as file(s)? It will save me the time of typing all the data.
Hi @sturlaws I really cound't attach. I am Writting an example
NotificationID | Date_Schedule | DateRead | Date_Dispatch | appid |
123 | 2020-01-27 10:45:00 | 2020-02-26 17:20:12 | 2020-01-27 10:59:22 | 6 |
173 | 2020-02-10 15:30:00 | 2020-02-26 17:20:12 | 2020-02-10 15:42:31 | 6 |
733 | 2020-04-17 18:30:00 | 2020-04-17 18:30:23 | 2020-04-17 18:34:42 | 6 |
734 | 2020-04-19 18:30:00 | 2020-04-19 18:30:50 | 2020-04-19 18:31:33 | 6 |
735 | 2020-04-19 18:30:00 | 2020-04-19 18:30:53 | 2020-04-19 18:31:50 | 6 |
NotificationID | UserID | StatusID |
123 | 92132 | 1 |
123 | 95259 | 1 |
123 | 107170 | 1 |
173 | 79137 | 1 |
173 | 79194 | 1 |
173 | 79286 | 1 |
733 | 76698 | 1 |
733 | 77767 | 1 |
733 | 77930 | 1 |
733 | 77939 | 1 |
733 | 78120 | 1 |
735 | 256292 | 1 |
735 | 256774 | 1 |
735 | 257702 | 1 |
123 | 230571 | 2 |
123 | 97722 | 2 |
173 | 79478 | 2 |
733 | 77780 | 0 |
733 | 79396 | 0 |
734 | 78977 | 0 |
734 | 79275 | 0 |
734 | 79736 | 0 |
734 | 92930 | 0 |
734 | 98586 | 0 |
SaleID | value | Discount | Date | ComandID | UserID | TypeID |
1994 | 16 | 27/01/2020 05:31 | 6503 | 107170 | 2 | |
1881 | 35 | 27/01/2020 01:23 | 6285 | 79137 | 1 | |
1877 | 66 | 27/01/2020 19:47 | 6278 | 79286 | 2 | |
1879 | 15 | 28/01/2020 19:58 | 6280 | 79137 | 2 | |
1876 | 35 | 27/01/2020 04:25 | 6273 | 79137 | 2 | |
1686 | 55,55 | 28/01/2020 06:28 | 5969 | 92132 | 2 | |
1631 | 31,58 | 28/01/2020 02:43 | 5881 | 76698 | 3 | |
1605 | 29,25 | 28/01/2020 08:40 | 5846 | 79194 | 2 | |
1608 | 24,9 | 28/01/2020 23:09 | 5854 | 79194 | 2 | |
1661 | 20 | 28/01/2020 00:56 | 5931 | 79286 | 2 | |
1840 | 19 | 28/01/2020 16:33 | 6212 | 92132 | 2 | |
1596 | 125,4 | 28/01/2020 07:59 | 5834 | 77930 | 2 | |
1581 | 100 | 27/01/2020 10:33 | 5799 | 77930 | 1 | |
1580 | 124,3 | 28/01/2020 00:22 | 5797 | 79137 | 2 | |
1569 | 20 | 27/01/2020 02:55 | 5782 | 79286 | 2 | |
1619 | 20 | 27/01/2020 11:57 | 5868 | 107170 | 2 | |
1699 | 22,9 | 28/01/2020 21:25 | 5995 | 77767 | 2 | |
1853 | 22,9 | 01/01/2020 20:51 | 6230 | 79286 | 2 | |
1573 | 48 | 01/01/2020 22:09 | 5787 | 79194 | 3 | |
1553 | 52,45 | 01/01/2020 18:17 | 5763 | 79137 | 1 | |
1550 | 12 | 10/01/2020 14:43 | 5760 | 79137 | 3 | |
1552 | 5,5 | 23/02/2020 12:18 | 5762 | 79194 | 3 | |
1896 | 22,9 | 06/02/2020 12:23 | 6317 | 107170 | 3 | |
1547 | 41,8 | 20/02/2020 21:32 | 5747 | 76698 | 2 | |
1546 | 36,3 | 20/02/2020 14:30 | 5743 | 79194 | 3 | |
1545 | 35,2 | 20/02/2020 14:29 | 5743 | 79194 | 3 | |
1540 | 61 | 20/02/2020 12:24 | 5736 | 95259 | 2 | |
1660 | 16,9 | 07/02/2020 12:05 | 5928 | 79137 | 2 | |
1695 | 20,9 | 13/02/2020 12:08 | 5991 | 76698 | 2 | |
1851 | 17,9 | 29/02/2020 12:21 | 6229 | 79286 | 2 | |
1544 | 26,4 | 20/02/2020 13:32 | 5740 | 95259 | 2 | |
1609 | 16,9 | 31/03/2020 13:00 | 5855 | 79194 | 2 | |
1672 | 16,9 | 08/04/2020 13:43 | 5944 | 79286 | 2 | |
1725 | 17,9 | 17/04/2020 13:25 | 6031 | 107170 | 2 | |
1527 | 52,8 | 19/04/2020 13:33 | 5711 | 79286 | 3 | |
1541 | 17,9 | 20/05/2020 12:31 | 5737 | 107170 | 1 | |
1512 | 29,7 | 19/04/2020 19:11 | 5694 | 77767 | 2 | |
1511 | 37,4 | 19/04/2020 19:11 | 5694 | 92132 | 2 | |
1502 | 14,9 | 18/04/2020 12:01 | 5681 | 107170 | 3 | |
1483 | 26 | 19/04/2020 21:19 | 5654 | 79137 | 3 | |
1649 | 26 | 19/04/2020 20:09 | 5912 | 79194 | 3 | |
1751 | 26 | 17/04/2020 18:24 | 6070 | 79194 | 3 | |
1880 | 26 | 20/04/2020 14:55 | 6284 | 95259 | 3 | |
1448 | 20 | 17/04/2020 09:24 | 5578 | 79137 | 2 | |
1443 | 18,9 | 18/04/2020 20:08 | 5572 | 77767 | 2 | |
1446 | 17,9 | 20/04/2020 19:56 | 5575 | 79286 | 2 | |
1473 | 16,9 | 20/04/2020 17:37 | 5628 | 79137 | 2 | |
1568 | 20,9 | 20/04/2020 18:44 | 5781 | 107170 | 2 | |
1628 | 16,9 | 18/04/2020 08:14 | 5880 | 79286 | 2 | |
1494 | 5,5 | 19/04/2020 09:20 | 5673 | 76698 | 2 | |
1496 | 17,5 | 19/04/2020 02:19 | 5675 | 79137 | 2 | |
1535 | 10,25 | 17/04/2020 13:32 | 5728 | 79137 | 2 | |
1537 | 22,9 | 18/04/2020 19:46 | 5730 | 95259 | 2 | |
1548 | 8,25 | 18/04/2020 08:13 | 5758 | 95259 | 2 | |
1554 | 3 | 20/04/2020 12:57 | 5764 | 77767 | 2 | |
1562 | 20 | 17/04/2020 23:07 | 5772 | 107170 | 2 | |
1567 | 9 | 20/04/2020 13:17 | 5780 | 79286 | 2 | |
1576 | 9 | 20/04/2020 23:13 | 5790 | 79137 | 2 | |
1602 | 36,8 | 20/04/2020 10:55 | 5843 | 77767 | 2 | |
1603 | 7,5 | 17/04/2020 04:37 | 5844 | 92132 | 2 | |
1625 | 6,5 | 17/04/2020 19:24 | 5877 | 92132 | 2 | |
1633 | 17,5 | 18/04/2020 16:00 | 5886 | 107170 | 2 | |
1634 | 8,5 | 20/04/2020 01:01 | 5887 | 79194 | 2 | |
1655 | 11,5 | 19/04/2020 01:33 | 5923 | 92132 | 2 | |
1656 | 15,5 | 17/04/2020 09:24 | 5924 | 79286 | 2 | |
1664 | 6 | 18/04/2020 04:39 | 5934 | 77767 | 2 | |
1682 | 3 | 19/04/2020 05:19 | 5960 | 79137 | 2 | |
1702 | 3 | 20/04/2020 14:37 | 5998 | 77939 | 2 | |
1708 | 14 | 20/04/2020 15:38 | 6006 | 107170 | 2 | |
1711 | 47,8 | 12,5 | 20/04/2020 10:47 | 6013 | 92132 | 2 |
1727 | 6 | 17/04/2020 20:11 | 6032 | 256774 | 2 | |
1740 | 9,5 | 20/04/2020 14:30 | 6048 | 76698 | 2 | |
1762 | 47,8 | 20/04/2020 06:44 | 6094 | 79194 | 2 | |
1779 | 12 | 17/04/2020 03:59 | 6113 | 92132 | 2 | |
1787 | 4,5 | 17/04/2020 22:19 | 6123 | 107170 | 2 | |
1809 | 17,5 | 10 | 17/04/2020 08:26 | 6151 | 79137 | 2 |
1817 | 3 | 20/04/2020 02:48 | 6164 | 79194 | 2 | |
1828 | 14,5 | 18/04/2020 03:55 | 6195 | 79286 | 2 | |
1857 | 16,5 | 17/04/2020 21:41 | 6239 | 79137 | 2 | |
1864 | 27 | 3 | 17/04/2020 11:27 | 6248 | 76698 | 2 |
1869 | 18,5 | 20/04/2020 10:59 | 6254 | 95259 | 2 | |
1889 | 53,8 | 18/04/2020 07:40 | 6300 | 77930 | 2 | |
1906 | 3,5 | 19/04/2020 04:16 | 6341 | 79286 | 2 | |
1927 | 21,5 | 18/04/2020 21:39 | 6385 | 77939 | 2 | |
1934 | 47,8 | 4,78 | 20/04/2020 15:14 | 6390 | 107170 | 2 |
1988 | 3 | 17/04/2020 13:26 | 6489 | 79286 | 2 | |
1996 | 47,8 | 17/04/2020 15:27 | 6509 | 79137 | 2 | |
1542 | 16,9 | 18/04/2020 03:17 | 5738 | 79194 | 2 | |
1445 | 34,1 | 19/04/2020 04:27 | 5573 | 92132 | 3 | |
1489 | 35,75 | 5,6 | 18/04/2020 11:00 | 5664 | 95259 | 3 |
1519 | 31,9 | 18/04/2020 03:58 | 5701 | 79286 | 3 | |
1621 | 31,9 | 19/04/2020 16:21 | 5871 | 107170 | 3 | |
1397 | 20 | 18/04/2020 00:44 | 5490 | 79286 | 2 | |
1412 | 21,9 | 17/04/2020 08:20 | 5524 | 95259 | 2 | |
1427 | 8,25 | 18/04/2020 14:31 | 5552 | 79194 | 2 | |
1435 | 6,75 | 19/04/2020 09:10 | 5560 | 79137 | 2 | |
1464 | 20 | 12 | 17/04/2020 23:08 | 5606 | 79286 | 2 |
1505 | 27,25 | 18/04/2020 12:30 | 5687 | 79137 | 2 | |
1563 | 20 | 19/04/2020 06:40 | 5773 | 77930 | 2 | |
1662 | 20 | 19/04/2020 10:10 | 5932 | 107170 | 2 | |
1671 | 20 | 20/04/2020 02:11 | 5943 | 76698 | 2 | |
1701 | 27,9 | 20/04/2020 08:39 | 5997 | 79137 | 2 | |
1713 | 22,9 | 20/04/2020 07:02 | 6016 | 79194 | 2 | |
1756 | 24,9 | 12 | 20/04/2020 07:17 | 6087 | 92132 | 2 |
1863 | 26,9 | 20/04/2020 19:18 | 6247 | 95259 | 2 | |
1410 | 16,9 | 19/04/2020 00:30 | 5516 | 107170 | 2 | |
1526 | 15,9 | 17/04/2020 05:51 | 5713 | 95259 | 2 | |
1387 | 20,9 | 18/04/2020 04:14 | 5463 | 95259 | 2 | |
1420 | 1 | 18/04/2020 22:29 | 5545 | 95259 | 2 | |
1641 | 50 | 19/04/2020 00:29 | 5892 | 76698 | 2 | |
1665 | 1 | 20/04/2020 13:53 | 5935 | 107170 | 2 | |
1668 | 1 | 20/04/2020 13:10 | 5937 | 77767 | 2 | |
1679 | 0,25 | 17/04/2020 09:52 | 5952 | 107170 | 2 | |
1680 | 0,25 | 18/04/2020 19:21 | 5953 | 79286 | 2 | |
1681 | 0,25 | 17/04/2020 15:22 | 5954 | 79137 | 2 | |
1688 | 1 | 20/04/2020 01:51 | 5972 | 77930 | 2 | |
1709 | 5,5 | 11/02/2020 20:23 | 6007 | 76698 | 2 | |
1715 | 1 | 10/02/2020 03:28 | 6018 | 95259 | 2 | |
1719 | 1 | 10/02/2020 22:58 | 6022 | 107170 | 2 | |
1755 | 1,5 | 1 | 12/02/2020 02:42 | 6085 | 79137 | 2 |
1766 | 1 | 10/02/2020 10:14 | 6101 | 92132 | 2 | |
1378 | 24,9 | 11/02/2020 04:57 | 5444 | 77767 | 3 | |
1463 | 24,9 | 10/02/2020 02:06 | 5604 | 79286 | 2 | |
1669 | 20,9 | 12/02/2020 14:54 | 5940 | 79194 | 2 | |
1712 | 26,9 | 10 | 12/02/2020 23:53 | 6015 | 79137 | 2 |
1515 | 6,5 | 2 | 12/02/2020 17:13 | 5697 | 107170 | 3 |
1577 | 68 | 11/02/2020 17:53 | 5802 | 77767 | 2 |
Nice.
A couple of things. I suspect that these sample data are simplified, perhaps to much. As long as each customer only has 1 notification, the relationships are 1-to-many. But once you have a customer with more than 1 notification the relationships between the tables will be many-to-many. Many-to-many relationships are much more complex to handle than 1-to-many.
You wrote that you want to analyse on the difference in time between dispatch and sales. How should the difference be calculated when there are more than 1 sale?
You were also looking for the difference between sales to customers who received notification to those that don't. But your sample sales data does not contain any customers who did not reveice notification.
What you could do is to do some calculations on your sample data in Excel to describe you desired output.
If you are having problem sharing the data directly in the forum, you can upload the data to dropbox/onedrive/other, and share the link
Hi @bcosta98 ,
To create relationship between tables should help you in your scenario.
https://docs.microsoft.com/en-us/power-bi/desktop-create-and-manage-relationships
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 |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |