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...
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,

     

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,