SUM() OVER..PARTITION BY in PowerBI?
Updated: Dec 14, 2022
This is the case I was facing in a project recently:
-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!