Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi!
I've been struggling to figure out how to calculate the information bellow. I would like to have an average of sales amount in an hour per distinct date.
Sale Datetime | Sale $ | Sale Hour | ||
08/03/2023 09:19 | 91 | 8 | ||
09/03/2023 09:23 | 95 | 8 | ||
09/03/2023 09:23 | 200 | 8 | ||
09/03/2023 09:28 | 180 | 8 | ||
08/03/2023 09:32 | 25 | 9 | ||
09/03/2023 09:07 | 108 | 9 | ||
09/03/2023 09:16 | 290 | 9 | ||
09/03/2023 09:31 | 107 | 9 | ||
09/03/2023 09:59 | 209 | 9 | ||
08/03/2023 09:49 | 175 | 9 | ||
08/03/2023 09:51 | 180 | 9 | ||
09/03/2023 09:45 | 263 | 9 | ||
09/03/2023 09:49 | 256 | 9 | ||
09/03/2023 09:36 | 44 | 9 | ||
09/03/2023 09:41 | 121 | 9 | ||
09/03/2023 09:43 | 45 | 9 |
In the data above, there are 4 sales at hour 8 (for my purpose, sale hour is 30 min before), three on March 9th and one on March 8th. When a do a simple measure to get an average per hour, the result is: 91+95+200+180 / 4 = 141.5 . However, since I need the average on distinct days, I was expecting 91 + 95 + 200 + 180 / 2, as there are only 2 different days.
May someone help me?
Solved! Go to Solution.
Hi. See if it helps
First in power query extract only date
Then you can create a measure in DAX
Hi. See if it helps
First in power query extract only date
Then you can create a measure in DAX
Already tried my best, but still could not find a solution
User | Count |
---|---|
93 | |
83 | |
77 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |