cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
o59393
Post Prodigy
Post Prodigy

Sumx with power query

Hi all

 

Is it possible to do this calculated column with Power Query?

 

 

Mix by product = 

DIVIDE(

CALCULATE(
    SUMX(
        'SKU by line - Official',
        'SKU by line - Official'[Production by bottler]
    )
)
,
CALCULATE(
    SUMX(
        'SKU by line - Official','SKU by line - Official'[Production by bottler]),
        ALLEXCEPT(
            'SKU by line - Official',
            'SKU by line - Official'[Manufacturing plant of production],
            'SKU by line - Official'[Manufacturing line],
            'SKU by line - Official'[Date]
        )
    )
)

 

 

The table is called SKU by line. 

 

In excel it looks like:

 

o59393_0-1631294671151.png

 

 

Here is the pbix: https://1drv.ms/u/s!ApgeWwGTKtFdh13Pj8JD18lE9HC8?e=eSBaJi

 

Thanks community.

1 ACCEPTED SOLUTION
jennratten
Super User
Super User

Hello - yes, this can be done in Power Query.  Here is an example...

 

Goal: Add a column that contains the value of the current ListPrice column divided by the sum of the ListPrice column for all records with matching values in the Color, Size and Style columns.

 

BEFORE

jennratten_1-1631300155348.png

 

AFTER

jennratten_2-1631300171411.png

 

SCRIPT (comments are included that explain what's going on).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZbLjpswFIZfxco6QQRsDNtUmqkqe9N0GqrpLGjkSVBcqAJM1bcvtqEDsY+plEXkSMn3n/9cfHl+Xj2h1Xp1OJet6Ffef6Jtqr7VXdUWZYV25UWgfX28NGukfs4CEq5e1jcg02AGgcwCeSfbsl/3CowSJVFXp81eCvEmEKtPNfokro34s0bqLzgNQgrR9C46XaSzIMtsWtcqynw0h2NrOg7voreLtNu5blYc+WgGxzZ0fBeNF2m389zgxIfnnugD7x23/+C9A5fP/XPF72RxvAwTh7Gyz0X1vQvDiDZo/6u+tv1yVosZOeLGdU9J7MU5jGtjBHtxBuMmMeJ3n88FDlb2RBX/UP98V/hSns5j4hQ7SZM4hUgOk8ZzCpFsRvLb0wGTbJ7trvyxmTYqzZys9puEMMthVjtOtjDLZuyTVeFE7eyPhXzdPJTVSVzRo6zfxGA4ioO+gwAY+0AcYEdEkykGQA5HNCDxge6Ipj4JADI4ogGpD3RHNMVRM/TQSekqTkyd7TA5ZgDIYVBbpSEAMhfYidEpVbPzQRZNUx7RV9G0xmMSzy7ggdAWaWQRHCSMt9gi2IywNz7Ft5vw31NhuqMS99lhfJJFCQ5LGOPW8WNJsJmE9T7Cqarv5+LYN2X6MEqdhLlMIotgFvFNSFn/HquVqnHTjsZLZqz29GlCYreE8ZktSXCPhLnFwiUJ5pEYbsLtkkY+EXn5Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Style = _t, Color = _t, Size = _t, ProductKey = _t, EnglishProductName = _t, ListPrice = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"ProductKey", Int64.Type}, {"EnglishProductName", type text}, {"Color", type text}, {"ListPrice", type number}, {"Size", type text}}),
    Grouped = Table.Group(
        ChangeType, 
        {"Style", "Color", "Size"},                           // group rows by these columns
        {
            {"GroupedRows",                                   // name of the column with the grouped tables
                each                                          // current iteration of the table...
                let                                           // create some variables
                    varTable = Table.Buffer ( _ ),            // read the current nested table into memory
                    varSum = List.Sum([ListPrice])            // denominator 
                in  
                    Table.AddColumn (                         // add a new column
                        varTable,                             // to the grouped table
                        "Calculation",                        // new column name
                        each Value.Divide (                   // new column value
                            Record.Field ( _, "ListPrice" ),  // current row of the nested table
                            varSum                            // sum of grouped value
                        ),
                        type table
                    )
            }
        }
    ),
    // Expand all columns in the nested tables that are not present in the Grouped table. 
    expand = Table.ExpandTableColumn (
        Grouped,
        "GroupedRows", 
        List.Difference ( 
            Table.ColumnNames ( Table.Combine ( Grouped[GroupedRows] ) ),
            Table.ColumnNames ( Grouped )
        )
    ),
    #"Sorted Rows" = Table.Sort(expand,{{"ProductKey", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"ProductKey", Int64.Type}, {"EnglishProductName", type text}, {"ListPrice", Currency.Type}, {"Calculation", Percentage.Type}})
in
    #"Changed Type"

View solution in original post

3 REPLIES 3
jennratten
Super User
Super User

Hello - yes, this can be done in Power Query.  Here is an example...

 

Goal: Add a column that contains the value of the current ListPrice column divided by the sum of the ListPrice column for all records with matching values in the Color, Size and Style columns.

 

BEFORE

jennratten_1-1631300155348.png

 

AFTER

jennratten_2-1631300171411.png

 

SCRIPT (comments are included that explain what's going on).

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("pZbLjpswFIZfxco6QQRsDNtUmqkqe9N0GqrpLGjkSVBcqAJM1bcvtqEDsY+plEXkSMn3n/9cfHl+Xj2h1Xp1OJet6Ffef6Jtqr7VXdUWZYV25UWgfX28NGukfs4CEq5e1jcg02AGgcwCeSfbsl/3CowSJVFXp81eCvEmEKtPNfokro34s0bqLzgNQgrR9C46XaSzIMtsWtcqynw0h2NrOg7voreLtNu5blYc+WgGxzZ0fBeNF2m389zgxIfnnugD7x23/+C9A5fP/XPF72RxvAwTh7Gyz0X1vQvDiDZo/6u+tv1yVosZOeLGdU9J7MU5jGtjBHtxBuMmMeJ3n88FDlb2RBX/UP98V/hSns5j4hQ7SZM4hUgOk8ZzCpFsRvLb0wGTbJ7trvyxmTYqzZys9puEMMthVjtOtjDLZuyTVeFE7eyPhXzdPJTVSVzRo6zfxGA4ioO+gwAY+0AcYEdEkykGQA5HNCDxge6Ipj4JADI4ogGpD3RHNMVRM/TQSekqTkyd7TA5ZgDIYVBbpSEAMhfYidEpVbPzQRZNUx7RV9G0xmMSzy7ggdAWaWQRHCSMt9gi2IywNz7Ft5vw31NhuqMS99lhfJJFCQ5LGOPW8WNJsJmE9T7Cqarv5+LYN2X6MEqdhLlMIotgFvFNSFn/HquVqnHTjsZLZqz29GlCYreE8ZktSXCPhLnFwiUJ5pEYbsLtkkY+EXn5Cw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Style = _t, Color = _t, Size = _t, ProductKey = _t, EnglishProductName = _t, ListPrice = _t]),
    ChangeType = Table.TransformColumnTypes(Source,{{"ProductKey", Int64.Type}, {"EnglishProductName", type text}, {"Color", type text}, {"ListPrice", type number}, {"Size", type text}}),
    Grouped = Table.Group(
        ChangeType, 
        {"Style", "Color", "Size"},                           // group rows by these columns
        {
            {"GroupedRows",                                   // name of the column with the grouped tables
                each                                          // current iteration of the table...
                let                                           // create some variables
                    varTable = Table.Buffer ( _ ),            // read the current nested table into memory
                    varSum = List.Sum([ListPrice])            // denominator 
                in  
                    Table.AddColumn (                         // add a new column
                        varTable,                             // to the grouped table
                        "Calculation",                        // new column name
                        each Value.Divide (                   // new column value
                            Record.Field ( _, "ListPrice" ),  // current row of the nested table
                            varSum                            // sum of grouped value
                        ),
                        type table
                    )
            }
        }
    ),
    // Expand all columns in the nested tables that are not present in the Grouped table. 
    expand = Table.ExpandTableColumn (
        Grouped,
        "GroupedRows", 
        List.Difference ( 
            Table.ColumnNames ( Table.Combine ( Grouped[GroupedRows] ) ),
            Table.ColumnNames ( Grouped )
        )
    ),
    #"Sorted Rows" = Table.Sort(expand,{{"ProductKey", Order.Ascending}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"ProductKey", Int64.Type}, {"EnglishProductName", type text}, {"ListPrice", Currency.Type}, {"Calculation", Percentage.Type}})
in
    #"Changed Type"

Hi


Can you share your pbix to see the steps?

 

THanks.

Sure  - you can also just create a blank query in your pbix, copy the script in its entirety, paste it into your blank query, and click ok.

 

 Recreate Excel SUMIFS in Power Query.pbix 

 

 

Helpful resources

Announcements
PBI Sept Update Carousel

Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

Learn Live

Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

MPPC 2023 PBI Carousel

Power Platform Conference-Power BI and Fabric Sessions

Join us Oct 1 - 6 in Las Vegas for the Microsoft Power Platform Conference.

Top Solution Authors
Top Kudoed Authors