cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

New Member

## Find max amount between date range by grouping columns and sum that max amount by grouping columns.

Good day friends.

I have dataset below in Power Bi Desktop.

I need determine max amount between a date range by grouping ID, GroupUser. After that sum max amount (between same date range) by grouping Group and User.

i.e

Let's working on it between 02.01.2022 and 05.01.2022

1.The max amount of ID AAA and BBB by grouping  ID, GroupUser

ID AAA

 ID Group User Max amount AAA Hard Josh 40 AAA Hard Alisa 30 AAA Soft Josh 10

ID BBB

 ID Group User Max amount BBB Hard Alisa 90

We have found max amount by grouping ID, GroupUser between 02.01.2022 and 05.01.2022 date range.

2.Now, we have to sum max amount by grouping Group, User between 02.01.2022 and 05.01.2022 date range.

Last result should be like below

 Group User Sum max amount Hard Josh 40 Soft Josh 10 Hard Alisa 120

Note: Result of max amount and summing max amount (by grouping  ID, Group, User and Group, User) should be flexible/changeable when change the date range between.

Lets give an example date range between 04.01.2022 and 05.01.2022

1.Finding max amount by grouping  ID, Group, User

 ID Group User Max amount AAA Soft Josh 10 BBB Hard Alisa 90

2.Summing max amount by grouping Group, User

 Group User Sum max amount Soft Josh 10 Hard Alisa 90

That's all.

1 ACCEPTED SOLUTION
Community Support

Hi @Garib ,

You create the following measures.

``````The max amount of ID AAA and BBB by grouping  ID, Group, User =
CALCULATE (
MAX ( 'Table'[Max amount] ),
FILTER (
ALLSELECTED ( 'Table' ),
[ID] = MAX ( 'Table'[ID] )
&& [Group] = MAX ( 'Table'[Group] )
&& [User] = MAX ( 'Table'[User] )
)
)
``````

``````The max amount by grouping Group and User =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Group],
'Table'[User],
'Table'[ID],
"SumMax",
MAXX (
'Table',
[The max amount of ID AAA and BBB by grouping  ID, Group, User]
)
)
RETURN
SUMX ( _table, [SumMax] )
``````

The above measures are the results of dynamic filtering by the date slicer, if you want to hardcode, you can also write the date range into the formula.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
New Member

Worked. Thanks so much.

Community Support

Hi @Garib ,

You create the following measures.

``````The max amount of ID AAA and BBB by grouping  ID, Group, User =
CALCULATE (
MAX ( 'Table'[Max amount] ),
FILTER (
ALLSELECTED ( 'Table' ),
[ID] = MAX ( 'Table'[ID] )
&& [Group] = MAX ( 'Table'[Group] )
&& [User] = MAX ( 'Table'[User] )
)
)
``````

``````The max amount by grouping Group and User =
VAR _table =
SUMMARIZE (
'Table',
'Table'[Group],
'Table'[User],
'Table'[ID],
"SumMax",
MAXX (
'Table',
[The max amount of ID AAA and BBB by grouping  ID, Group, User]
)
)
RETURN
SUMX ( _table, [SumMax] )
``````

The above measures are the results of dynamic filtering by the date slicer, if you want to hardcode, you can also write the date range into the formula.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Top Solution Authors
Top Kudoed Authors