Sequence by multiple columns

[Anonymous]

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

[v-lid-msft · Accepted 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,

[vanessafvg]

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