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

Question

Good day friends.I have dataset below in Power Bi Desktop.I need determine max amount between a date range by grouping&nbsp;ID, Group,&nbsp;User. After that sum max amount&nbsp;(between same date range) by grouping&nbsp;Group&nbsp;and&nbsp;User.&nbsp;&nbsp;&nbsp;i.eLet's working on it between 02.01.2022 and&nbsp;05.01.20221.The max amount of ID AAA&nbsp;and BBB by grouping&nbsp;&nbsp;ID, Group,&nbsp;User&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ID AAAIDGroup&nbsp;User&nbsp;Max amountAAAHard&nbsp;Josh&nbsp;40AAAHard&nbsp;Alisa&nbsp;30AAASoft&nbsp;Josh&nbsp;10&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ID BBBIDGroup&nbsp;User&nbsp;Max amountBBBHard&nbsp;Alisa&nbsp;90&nbsp;We have found max amount by grouping&nbsp;ID, Group,&nbsp;User between&nbsp;02.01.2022 and&nbsp;05.01.2022 date range.&nbsp;2.Now, we have to sum max amount by&nbsp;grouping&nbsp;Group,&nbsp;User between&nbsp;02.01.2022 and&nbsp;05.01.2022 date range.&nbsp; &nbsp;Last result should be like belowGroup&nbsp;User&nbsp;Sum max amountHard&nbsp;Josh&nbsp;40Soft&nbsp;Josh&nbsp;10Hard&nbsp;Alisa&nbsp;120&nbsp;Note:&nbsp;Result of max amount and summing max amount (by grouping &nbsp;ID, Group,&nbsp;User and&nbsp;Group,&nbsp;User) should be flexible/changeable when change the date range between.&nbsp;Lets give an example date range between&nbsp;04.01.2022 and&nbsp;05.01.20221.Finding max amount&nbsp;by grouping&nbsp;&nbsp;ID, Group,&nbsp;User&nbsp;IDGroup&nbsp;User&nbsp;Max amountAAASoft&nbsp;Josh&nbsp;10BBBHard&nbsp;Alisa&nbsp;90&nbsp;2.Summing max amount by&nbsp;grouping&nbsp;Group,&nbsp;UserGroup&nbsp;User&nbsp;Sum max amountSoft&nbsp;Josh&nbsp;10Hard&nbsp;Alisa&nbsp;90&nbsp;That's all.&nbsp; Thanks in advance&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

anonymous · Accepted Answer

Hi&nbsp;Garib&nbsp;,
&nbsp;
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] )
            &amp;&amp; [Group] = MAX ( 'Table'[Group] )
            &amp;&amp; [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.
You can download the attachment for more details
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;
&nbsp;
&nbsp;

Forum Discussion

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

2 Replies

Related content

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience

Create columns group

Grouping Columns

Grouping rows by Column and then updating Column

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

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