SUM() OVER..PARTITION BY in PowerBI?

This is the case I was facing in a project recently:

I had

-a unique product ID,

-a common ProductCode & ProductName and

-a specific quantity for each.

On Dataverse, this was two rows in the Products entity.

I needed the TotalQuantity per ProductCode , to perform a specific calculation for the pricing of each ProductCode.

So, I practically needed a new Column (here: 'TotalQuantity') which would sum the Quantity Column per Product Code.

This is what my table looked like in Excel:

Looks familiar? In SQL Server , I would achieve this with SUM() OVER....PARTITION BY : Check out this blog post about this clause.

You can do the same thing with a Measure in Power BI and this is the Syntax:

TotalQuantity = CALCULATE(SUM(QuantityFieldNameinyourTable); FILTER(ALL('YourTableName'); [ProductCodeFieldName] = MIN(ProductCodeFieldName) && [ProductNameFieldName] = MIN([ProductNameFieldName] )&& etc)

II NOTE! Before applying this solution (which actually works!) please refer to Microsoft Learn https://learn.microsoft.com/en-us/dax/best-practices/dax-avoid-avoid-filter-as-filter-argument where it is recommended to use KEEPFILTERS instead!

Awesome stuff.