Forum Discussion

RyanS32229's avatar
RyanS32229
Frequent Visitor
8 years ago

Help with Grouping by account

Hey,

 

Some context: We have a table with contracts, including the Account ID of the contract's account, and a Contract ID. Each Account can have multiple contracts in the table, but not at the same time, i.e. they can start a new contract if they close their old contract.

 

I want to calculate a sum of the amount of each account based on their MOST RECENT contract. We have a field for contract number, so effectively need to:

 

Group by Account ID, showing just the row for the highest contract number of that account.

 

I can then sum the contract amount to find the Amount we have in current contracts.

 

I have been trying this through GROUPBY and SUMMARIZECOLUMNS, but I cannot get either of these to work.

 

All help appreciated.

Need Help

3 Replies

  • shebr's avatar
    shebr
    Icon for Resolver III rankResolver III
    8 years ago

    Hi RyanS32229

     

    If you are able to add a flag on your contract table, 1 or 'Active' for current, 0 or 'Expired' for expired, you can then filter for only the Active contracts. You then should just be able to select both columns and see a sum of the contract value.

     

    Does that help?

     

    Thanks

     

    shebr

    • RyanS32229's avatar
      RyanS32229
      Frequent Visitor
      8 years ago

      shebr True!

       

      So I'd need to make a calculated column. Something like

       

      IF({Contract number = Max contract number for that Account ID}, 1, 0)

       

      Any ideas on how I could design the expression?

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    RyanS32229,

    Please share sample data of your table and post expected result here so that we can provide you appropriate DAX.

    Regards,
    Lydia