cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RyanS32229 Frequent Visitor
Frequent Visitor

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.

3 REPLIES 3
shebr Member
Member

Re: Help with Grouping by account

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 Frequent Visitor
Frequent Visitor

Re: Help with Grouping by account

@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?

Moderator v-yuezhe-msft
Moderator

Re: Help with Grouping by account

@RyanS32229,

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 207 members 1,997 guests
Please welcome our newest community members: