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.
Sorry for the really abstract title but this is a bit of a strange one.
We have this :
For the first 2 lines you see there is 12 seconds between them.
I made Bins to group everything in the same minute because I want the EA/Carton field to show 1.
So if it was confirmed in the same minute it is only 1 occurence.
When I take out confirmation time everything looks ok :
2:27 showing only 1 EA/Carton. But the field is not summed. And of course this is only 1 line.
In the end I want to sum the occurences and make sure that everything in the same minute is counted as 1.
But when I put sum back on on the field EA/Carton :
Then it still counts as 2.
Would there be a good way to count everything that happens in the same minute as 1 occurence?
Here a snippet test data to play around with :
Confirmation time | Confirmation time 1 Min Bin | Source Storage Bin | EA/Carton | Sum of Act.qty (dest) |
02:27:18 | 02:27:00 | CQ0505A | 1 | 1 |
02:27:30 | 02:27:00 | CQ0505A | 1 | 1 |
05:32:55 | 05:32:00 | CQ0505A | 1 | 1 |
10:36:36 | 10:36:00 | CQ0505A | 1 | 1 |
10:36:51 | 10:36:00 | CQ0505A | 1 | 1 |
12:00:48 | 12:00:00 | CQ0505A | 1 | 1 |
12:23:14 | 12:23:00 | CQ0505A | 1 | 2 |
12:23:32 | 12:23:00 | CQ0505A | 1 | 1 |
12:40:47 | 12:40:00 | CQ0505A | 1 | 1 |
12:47:08 | 12:47:00 | CQ0505A | 1 | 1 |
14:03:06 | 14:03:00 | CQ0505A | 1 | 1 |
14:37:35 | 14:37:00 | CQ0505A | 1 | 1 |
14:39:01 | 14:39:00 | CQ0505A | 1 | 1 |
14:53:28 | 14:53:00 | CQ0505A | 1 | 1 |
14:56:32 | 14:56:00 | CQ0505A | 1 | 2 |
15:05:23 | 15:05:00 | CQ0505A | 1 | 1 |
15:35:18 | 15:35:00 | CQ0505A | 1 | 1 |
15:35:28 | 15:35:00 | CQ0505A | 1 | 1 |
15:35:41 | 15:35:00 | CQ0505A | 1 | 1 |
15:35:52 | 15:35:00 | CQ0505A | 1 | 1 |
15:36:06 | 15:36:00 | CQ0505A | 1 | 1 |
Solved! Go to Solution.
rpinxt,
Got it. One solution you can try is:
Sum of EA/Carton =
VAR Summarized =
SUMMARIZE (
Table1,
Table1[Confirmation time 1 Min Bin], Table1[Source Storage Bin], Table1[EA/Carton]
)
VAR Result =
SUMX (
Summarized,
[EA/Carton]
)
RETURN Result
What this does is do the aggregation first before summing the EA/Carton column. I wasn't fully clear on what you were looking for as an end result so let me know if I'm a little off the mark. The overall concept is probably correct though.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
Thanks but that is not an option. Data is coming from our system and date/time comes in that format.
Also this is only small part of the views. In other views we would like precise timing.
rpinxt,
Got it. One solution you can try is:
Sum of EA/Carton =
VAR Summarized =
SUMMARIZE (
Table1,
Table1[Confirmation time 1 Min Bin], Table1[Source Storage Bin], Table1[EA/Carton]
)
VAR Result =
SUMX (
Summarized,
[EA/Carton]
)
RETURN Result
What this does is do the aggregation first before summing the EA/Carton column. I wasn't fully clear on what you were looking for as an end result so let me know if I'm a little off the mark. The overall concept is probably correct though.
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
Hi rpinxt,
Do you ever care about the seconds in the confirmation time? Will you ever need to analyze data at the second level of granularity? If not, the easiest solution is to fix the grain of your fact table in the data source so you don't have two different lines for 02:27 in your data model in the first place
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
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 |
---|---|
114 | |
105 | |
79 | |
68 | |
63 |
User | Count |
---|---|
148 | |
107 | |
106 | |
82 | |
70 |