Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summarizing table sales

I have order table like    CustomerId OrderAmount Orderdatetime OrderAmountCurrency AAA 120 02/03/2022 02:03 US$120 AAA 20 02/03/2022 02:20 US$20 AAA 320 03/03/2022 03:03 US...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    You can do this in a table visual like this:

     

    The InitialOrder and FinalOrder measures are defined like this:

    FinalOrder = 
VAR OrderDate = MAX ('Table'[Orderdatetime] ) /*Use MIN instead for InitialOrder*/
RETURN
    CALCULATE (
        SELECTEDVALUE ('Table'[OrderAmountCurrency] ),
        'Table'[Orderdatetime] = OrderDate
    )
sanalytics's avatar
sanalytics
Super User
4 years ago

Anonymous 

 

Below is the PQ solution

Main Output

let
Source = Data,
#"Filtered Rows" = Table.SelectRows(Source, each ([OrderAmount] <> "20")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"CustomerId"}, {{"Count", each List.Min([Orderdatetime]), type nullable datetime}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"CustomerId", "Count"}, Data, {"CustomerId", "Orderdatetime"}, "Data", JoinKind.LeftOuter),
#"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"OrderAmountCurrency"}, {"Data.OrderAmountCurrency"}),
#"Merged Queries1" = Table.NestedJoin(#"Expanded Data", {"CustomerId"}, #"Data (3)", {"CustomerId"}, "Data (3)", JoinKind.LeftOuter),
#"Expanded Data (3)" = Table.ExpandTableColumn(#"Merged Queries1", "Data (3)", {"Data.OrderAmountCurrency"}, {"Data (3).Data.OrderAmountCurrency"}),
#"Renamed Columns" = Table.RenameColumns(#"Expanded Data (3)",{{"Count", "Orderdate"}, {"Data.OrderAmountCurrency", "Initial Order"}, {"Data (3).Data.OrderAmountCurrency", "Final Order"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns",{"CustomerId", "Initial Order", "Orderdate", "Final Order"})
in
#"Reordered Columns"

 

For Data (3)
let
Source = Data,
#"Filtered Rows" = Table.SelectRows(Source, each ([OrderAmount] <> "20")),
#"Grouped Rows" = Table.Group(#"Filtered Rows", {"CustomerId"}, {{"Count", each List.Max([Orderdatetime]), type nullable datetime}}),
#"Merged Queries" = Table.NestedJoin(#"Grouped Rows", {"CustomerId", "Count"}, Data, {"CustomerId", "Orderdatetime"}, "Data", JoinKind.LeftOuter),
#"Expanded Data" = Table.ExpandTableColumn(#"Merged Queries", "Data", {"OrderAmountCurrency"}, {"Data.OrderAmountCurrency"})
in
#"Expanded Data"

 

Please note that, the above table can be achieved in DAX also..I did it power query quickly for time constraints.May be later, I will send you the DAX solution

 

Hope this will help you

 

Regards
sanalytics

 

if it is your solution then please like and accept it as your solution