Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I need some help with a new measure or calculcated colum/table. I'm not sure how to go about doing what I need to do. This is what I have at this point for my data. Column 1 is the sequence number, 2 is the part number, 3 is the date, 4 is the quantity needed at the start of the day, 5 is the quantity that has been delivered to the department since the start of the day and then this is where I have issues. I want the final column to show the total quantity left to be delivered for that specific sequence number. So for sequence 1, the total still needed is 36 but for sequence 6 (same part number) I want it to be a cumulative of sequence 1 and 6 so the total would be 92 and not 0. I'm not sure how to go about doing that. Another example would be sequence numbers 3 and 5, where I want 3 to show 0 needed and 5 to show we still need 36. Any help would be appreciated!!
Sequence | Item | Date | QuantityToBuild | Qty Delivered | Qty Needed | Total Needed for Day |
1 | 385CS1 | 5/18/2020 0:00 | 336 | 300 | 36 | 92 |
2 | 386CS1 | 5/18/2020 0:00 | 450 | 450 | 450 | |
3 | 387CS1 | 5/18/2020 0:00 | 112 | 300 | 0 | 36 |
4 | 2249CS1 | 5/18/2020 0:00 | 224 | 224 | 224 | |
5 | 387CS1 | 5/18/2020 0:00 | 224 | 300 | 0 | 36 |
6 | 385CS1 | 5/18/2020 0:00 | 56 | 300 | 0 | 92 |
7 | 2248CS1 | 5/18/2020 0:00 | 224 | 224 | 224 | |
8 | 4909CS1 | 5/18/2020 0:00 | 560 | 560 | 560 | |
9 | 4910CS1 | 5/18/2020 0:00 | 112 | 112 | 112 | |
10 | 9441CS1 | 5/18/2020 0:00 | 56 | 56 | 56 | |
11 | 9442CS1 | 5/18/2020 0:00 | 56 | 56 | 56 | |
12 | 6971CS1 | 5/18/2020 0:00 | 90 | 90 | 90 | |
13 | 6960CS1 | 5/18/2020 0:00 | 90 | 90 | 90 | |
14 | 4797CS1 | 5/18/2020 0:00 | 90 | 90 | 90 | |
15 | 7516CS1 | 5/18/2020 0:00 | 300 | 100 | 200 | 200 |
16 | 6950CS1 | 5/18/2020 0:00 | 30 | 30 | 30 | |
17 | 6955CS1 | 5/18/2020 0:00 | 30 | 30 | 30 | |
18 | 2647CS1 | 5/18/2020 0:00 | 30 | 100 | 0 | 0 |
FYI - the QTY delivered is from a different table and is being pulled in with a relationship tied to the Item.
Maybe these two columns:
CumulativeQuantityToBuild = SUMX(FILTER('Table (4)',[Sequence] <= EARLIER([Sequence]) && [Item] = EARLIER([Item])),[QuantityToBuild])
Qty Needed For Sequence =
VAR __Needed = [CumulativeQuantityToBuild] - [Qty Delivered]
RETURN
IF(__Needed < 0,0,__Needed)
I cant seem to get this to work - potentially because the main table I'm working off of is a calculated table? Not sure if that matters or not.....Maybe I need to add more to my already calculated table? Here is calculation for the current table I'm using
Table 2 =
VAR temp =
ADDCOLUMNS (
'Table',
"MinS",
VAR s = [Sequence]
VAR C = [Item]
RETURN
MINX (
FILTER (
'Table',
VAR c1 = [Item]
VAR s1 = [Sequence]
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[Item] = c1
&& 'Table'[Sequence] <= s
&& 'Table'[Sequence] >= s1
)
) = s - s1 + 1
),
[Sequence]
)
)
RETURN
SELECTCOLUMNS (
SUMMARIZE (
temp,
[MinS],
"Sequence", RANKX ( temp, [MinS],, ASC, DENSE ),
"Item", MAX ( 'Table'[Item] ),
"QuantityToBuild", SUM ( 'Table'[QuantityToBuild] ),
"Date", MAX ( 'Table'[Date] )
),
"Sequence", [Sequence],
"Item", [Item],
"QuantityToBuild", [QuantityToBuild],
"Date", [Date]
)
Do I just need to add to this?
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 |
---|---|
54 | |
26 | |
20 | |
15 | |
11 |
User | Count |
---|---|
77 | |
62 | |
44 | |
17 | |
12 |