Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Summarizing table sales

I have order table like 

 

CustomerIdOrderAmountOrderdatetimeOrderAmountCurrency
AAA12002/03/2022 02:03US$120
AAA2002/03/2022 02:20US$20
AAA32003/03/2022 03:03US$320
BBB30002/03/2022 02:03$300
BBB2002/03/2022 02:20$20
BBB20002/03/2022 03:03$200

I'd like to display a table like this: Customerid, initial order-The amount of the first attempt All purchases worth less than $ 40 should be excluded, Ordedate-the order of the first attempt; Final Order- the lastest attempt

 

CustomerIdInitialOrderOrderdateFinalOrder
AAAUS$12002/03/2022 02:03US$320
BBB$30002/03/2022 02:03$200

 

How to do it?

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
    )

2 Replies

  • 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

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    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
    )