Get the max of the sum total defect quantity

[amirabedhiafi]

I have this following query  :

SELECT * FROM (
SELECT Vendor,[Material Type]
,SUM([Total Defect Qty]) AS [Sum]
,YEAR([Date]) as [Date]
, RANK() OVER(PARTITION BY Vendor,YEAR([Date]) ORDER BY SUM([Total Defect Qty]) DESC) AS rn
FROM SupplierQuality
GROUP BY Vendor,[Material Type],YEAR([Date])
) AS T
WHERE T.rn=1 
ORDER BY Vendor,[Date],[Sum] DESC

I need to do the same in Power BI but my data source isn't a SQL Server database, it's an Excel file.

How can I get the max of the summed [Total Defect Qty] ?

I have the following data returned by the query above :

For example, for the vendor Abata I need to get only the max Sum for 2018 and 2019

[amirabedhiafi · Accepted solution]

I ended up creating a calculated table : 

GroupedTable = GROUPBY(

SupplierQuality,

SupplierQuality[Vendor],

SupplierQuality[Material Type],

SupplierQuality[Date].[Année],

"Sum Total Defect Qtity",

SUMX(

CURRENTGROUP(),

SupplierQuality[Total Defect Qty]

)

)

Then created a measure like below : 

Max in group = IF(CALCULATE(MAX(GroupedTable[Sum Total Defect Qtity])

,ALLEXCEPT(GroupedTable,

GroupedTable[SupplierQuality_Vendor]))=MAX(GroupedTable[Sum Total Defect Qtity])

,1,0)

[mahoneypat]

In this ribbon in the query editor, click on the Group By button, choose advanced and set up Group By on Vendor, Material Type, and Date columns, and then choose Max as the aggregation of the Sum column.

Regards,

Pat