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
Altaibali
New Member

Calculating the Max based on two criterias

Hello All,

 

I have a daily stock report that I combine in Power query, and the report after combining would look like this:

DateCalendar WeekContractProviderAvailable Stock
1/2/2023266666AOI30
1/2/2023255555AOI20
1/2/2023244444FBL60
1/3/2023266666AOI40
1/3/2023255555AOI15
1/3/2023244444FBL20

 

So the first 3 entries are from the report of 02.01.2023 and the next are from the report of 03.01.2023 and so on..

I need a dax formula to calculate the maximum stock available in every calendar week to use in a pivot table, by summing up the available stock for all the contracts everyday in the week and gives value of the maximum day, and your help on this is much appreciated.

 

Thank you,

Altaiba

1 REPLY 1
MAwwad
Super User
Super User

 

To calculate the maximum stock available in every calendar week, you can use the following DAX formula:

 

 
Max Stock Available = MAXX( ADDCOLUMNS( GROUPBY( Table1, Table1[Calendar Week], Table1[Contract] ), "Total Stock", SUM(Table1[Available Stock]) ), [Total Stock] )
 

This formula first groups the data by the Calendar Week and Contract columns, and calculates the total stock available for each combination using the SUM function. It then creates a new column called "Total Stock" to store the calculated value.

The formula then uses the MAXX function to iterate over the grouped data and return the maximum value of the "Total Stock" column.

You can add this formula to your data model and use it in a pivot table to get the maximum stock available for each calendar week.

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.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors