Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Sequence by multiple columns

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:

SequenceItemNumberComponentQuantityToBuildDate
117821282245/11/2020 0:00
217921281125/11/2020 0:00
322706312245/11/2020 0:00
41822128565/11/2020 0:00
51832128565/11/2020 0:00
622716312245/11/2020 0:00
7848584891125/11/2020 0:00
8848484895045/11/2020 0:00
918121281685/11/2020 0:00
103108924905/11/2020 0:00
1164139452565/11/2020 0:00
123107924905/11/2020 0:00
1364129452565/11/2020 0:00
143109924305/11/2020 0:00

 

Here is how I would hope my data would look: 

 

SequenceComponentQuantityToBuildDate
121283365/11/2020 0:00
26312245/11/2020 0:00
321281125/11/2020 0:00
46312245/11/2020 0:00
584896165/11/2020 0:00
621281685/11/2020 0:00
7924905/11/2020 0:00
89452565/11/2020 0:00
9924905/11/2020 0:00
109452565/11/2020 0:00
11924305/11/2020 0:00
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

2 Replies

  • v-lid-msft's avatar
    v-lid-msft
    Community Support
    6 years ago

    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,