cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kelly4dm Helper I
Helper I

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
1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Re: Sequence by multiple columns

Hi @kelly4dm ,

 

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]
    )

 

15.jpg


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
Super User I
Super User I

Re: Sequence by multiple columns

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


Did I answer your question? Mark my post as a solution.
Proud a to be a Datanaut!
Community Support
Community Support

Re: Sequence by multiple columns

Hi @kelly4dm ,

 

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]
    )

 

15.jpg


By the way, PBIX file as attached.


Best regards,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors