Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Garib
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. 

 

Garib_1-1676899844921.png

 

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

IDGroup User Max amount
AAAHard Josh 40
AAAHard Alisa 30
AAASoft Josh 10

 

                           ID BBB

IDGroup User Max amount
BBBHard 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 

IDGroup User Max amount
AAASoft Josh 10
BBBHard Alisa 90

 

2.Summing max amount by grouping Group, User

Group User Sum max amount
Soft Josh 10
Hard Alisa 90

 

That's all. 

Thanks in advance

 

 

 

 

 

 

1 ACCEPTED SOLUTION
v-stephen-msft
Community Support
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] )
    )
)

vstephenmsft_0-1677047426480.png

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] )

vstephenmsft_1-1677048430421.png

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

                                                                                                                                                         

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.           

 

 

View solution in original post

2 REPLIES 2
Garib
New Member

Worked. Thanks so much. 

v-stephen-msft
Community Support
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] )
    )
)

vstephenmsft_0-1677047426480.png

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] )

vstephenmsft_1-1677048430421.png

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

                                                                                                                                                         

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.           

 

 

Helpful resources

Announcements
Fabric Community Conference

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.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.