• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Summary Qty vs Price

AlanSidman

Well-Known Member
Using Power Query, bring each table into the PQ editor. Merge (Left Join) the two tables. Here is the Mcode for the Merge

Code:
let
    Source = Table.NestedJoin(Table1, {"Kode"}, Table3, {"Kode"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Qty in pcs"}, {"Qty in pcs"}),
    #"Inserted Multiplication" = Table.AddColumn(#"Expanded Table3", "Multiplication", each [#"Price/pcs"] * [Qty in pcs], Int64.Type),
    #"Grouped Rows" = Table.Group(#"Inserted Multiplication", {"Group"}, {{"Quantity", each List.Sum([Qty in pcs]), type nullable number}, {"Price", each List.Sum([#"Price/pcs"]), type nullable number}})
in
    #"Grouped Rows"
Data Range
A
B
C
1
Group​
Quantity​
Price​
2
Fruit​
80​
134​
3
Vegetable​
69​
69​
 

Attachments

stormania

Member
the result that i mean is total price after one by one qty of products time each price
Maybe any coomon formula solution?
 

bosco_yip

Excel Ninja
1] In "Total Recap Price" B5, formula copied down :

=SUMPRODUCT((price!$C$5:$C$10=A5)*price!$D$5:$D$10*qty!$D$5:$D$10)

2] In "Total Recap Quantity" F5, formula copied down :

=SUMIF(qty!C:C,E5,qty!D:D)

70991
 
Top