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.
I need to be able to sequence my data using the current sequence - but totaling the "QuantityToBuild" column by grouping by the "Component" Column.
So in otherwords I would need sequence 1 and 2 to be labeled as "1", Component "2128", and the QuantityToBuild "336". The go to sequence "2", Component "631", QuantityToBuild "224", and so on..
Here is my data currently as is:
Sequence | ItemNumber | Component | QuantityToBuild | Date |
1 | 178 | 2128 | 224 | 5/11/2020 0:00 |
2 | 179 | 2128 | 112 | 5/11/2020 0:00 |
3 | 2270 | 631 | 224 | 5/11/2020 0:00 |
4 | 182 | 2128 | 56 | 5/11/2020 0:00 |
5 | 183 | 2128 | 56 | 5/11/2020 0:00 |
6 | 2271 | 631 | 224 | 5/11/2020 0:00 |
7 | 8485 | 8489 | 112 | 5/11/2020 0:00 |
8 | 8484 | 8489 | 504 | 5/11/2020 0:00 |
9 | 181 | 2128 | 168 | 5/11/2020 0:00 |
10 | 3108 | 924 | 90 | 5/11/2020 0:00 |
11 | 6413 | 9452 | 56 | 5/11/2020 0:00 |
12 | 3107 | 924 | 90 | 5/11/2020 0:00 |
13 | 6412 | 9452 | 56 | 5/11/2020 0:00 |
14 | 3109 | 924 | 30 | 5/11/2020 0:00 |
Here is how I would hope my data would look:
Sequence | Component | QuantityToBuild | Date |
1 | 2128 | 336 | 5/11/2020 0:00 |
2 | 631 | 224 | 5/11/2020 0:00 |
3 | 2128 | 112 | 5/11/2020 0:00 |
4 | 631 | 224 | 5/11/2020 0:00 |
5 | 8489 | 616 | 5/11/2020 0:00 |
6 | 2128 | 168 | 5/11/2020 0:00 |
7 | 924 | 90 | 5/11/2020 0:00 |
8 | 9452 | 56 | 5/11/2020 0:00 |
9 | 924 | 90 | 5/11/2020 0:00 |
10 | 9452 | 56 | 5/11/2020 0:00 |
11 | 924 | 30 | 5/11/2020 0:00 |
Solved! Go to Solution.
Hi @Anonymous ,
We can create a calculated table to meet your requirement:
Table 2 =
VAR temp =
ADDCOLUMNS (
'Table',
"MinS",
VAR s = [Sequence]
VAR C = [Component]
RETURN
MINX (
FILTER (
'Table',
VAR c1 = [Component]
VAR s1 = [Sequence]
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[Component] = c1
&& 'Table'[Sequence] <= s
&& 'Table'[Sequence] >= s1
)
) = s - s1 + 1
),
[Sequence]
)
)
RETURN
SELECTCOLUMNS (
SUMMARIZE (
temp,
[MinS],
"Sequence", RANKX ( temp, [MinS],, ASC, DENSE ),
"Component", MAX ( 'Table'[Component] ),
"QuantityToBuild", SUM ( 'Table'[QuantityToBuild] ),
"Date", MAX ( 'Table'[Date] )
),
"Sequence", [Sequence],
"Component", [Component],
"QuantityToBuild", [QuantityToBuild],
"Date", [Date]
)
By the way, PBIX file as attached.
Best regards,
Hi @Anonymous ,
We can create a calculated table to meet your requirement:
Table 2 =
VAR temp =
ADDCOLUMNS (
'Table',
"MinS",
VAR s = [Sequence]
VAR C = [Component]
RETURN
MINX (
FILTER (
'Table',
VAR c1 = [Component]
VAR s1 = [Sequence]
RETURN
CALCULATE (
COUNTROWS ( 'Table' ),
FILTER (
ALL ( 'Table' ),
'Table'[Component] = c1
&& 'Table'[Sequence] <= s
&& 'Table'[Sequence] >= s1
)
) = s - s1 + 1
),
[Sequence]
)
)
RETURN
SELECTCOLUMNS (
SUMMARIZE (
temp,
[MinS],
"Sequence", RANKX ( temp, [MinS],, ASC, DENSE ),
"Component", MAX ( 'Table'[Component] ),
"QuantityToBuild", SUM ( 'Table'[QuantityToBuild] ),
"Date", MAX ( 'Table'[Date] )
),
"Sequence", [Sequence],
"Component", [Component],
"QuantityToBuild", [QuantityToBuild],
"Date", [Date]
)
By the way, PBIX file as attached.
Best regards,
click on the component field, then click on modelling table in your menu ribbon and under column tools click on sort by column, you can then use the sequence column to sort your component field
https://docs.microsoft.com/en-us/power-bi/desktop-sort-by-column
Proud to be a Super User!
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 |
---|---|
107 | |
98 | |
78 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |