Get max value in grouped column in PowerBI

Question

I have 2 tables, Orders and Items. Orders have Order,Account,Item and Qty information and Items have one record for each Item in the order. i.e. if there is 2 order for item A, then there will be 2 records in the Item table.

My tables are as follows

Order

    Order-ID    Order Number    Account Item    Qty
    1   100 ABC A   2
    2   100 ABC B   4
    3   100 ABC C   1
    4   200 XYZ A   1
    5   200 XYZ C   1
    6   300 LMO B   2
    7   300 LMO C   1
    8   400 ABC A   1
    9   400 ABC B   4
    10  400 ABC C   2
    11  400 ABC D   1
    12  500 LMO E   3

Outgoing

    Order-ID    Outgoing_ID Order Number    Item    Liquid
    1   1   100 A   Y
    1   2   100 A   Y
    2   3   100 B   N
    2   4   100 B   N
    2   5   100 B   N
    2   6   100 B   N
    3   7   100 C   Y
    4   8   200 A   Y
    5   9   200 C   Y
    6   10  300 B   N
    6   11  300 B   N
    7   12  300 C   Y
    8   13  400 A   Y
    9   14  400 B   N
    9   15  400 B   N
    9   16  400 B   N
    9   17  400 B   N
    10  18  400 C   Y
    10  19  400 C   Y
    11  20  400 D   N
    12  21  500 E   N
    12  22  500 E   N
    12  23  500 E   N

I want my output to show Accounts in Rows and Max of Liquid count by comparing all the orders for that account.

Required output

    Account Max-Liquid
    ABC 5
    XYZ 0
    LMO 3

And when I drill down, I should be able to get the total Liquid count for orders of respective accounts

Drill down content

    Intermediate table

    Account Order Number    Liquid
    ABC 100 4
    ABC 400 5
    XYZ 200 0
    LMO 500 3
    LMO 300 2

Any possible way to achieve this is a Measure? (or via columns)

Output required:

Answer 1

If I'm understanding correctly, I think you'll want a measure like this:

Liquid =
MAXX(VALUES('Order'[Order Number]),
    0 + CALCULATE(SUM('Order'[Qty]), Outgoing[Liquid] = "Y"))