Alter count calculation to show sum of individual results in matrix

[tdhlonghorn]

Hello,

I'm trying to calculate % of product in stock, and I have SKU and Department listed in the Rows. I would like to see the count at the SKU level, but ensure that the Department result is the sum of the individual SKU counts (instead of calculating it using the same SKU-level formula). 

The following formula produces the expected result by SKU; the problem is that for the Department it applies the same calculation, meaning every SKU is counted because the total inventory by department is > 5.

I assume some application of IF(HASONEVALUE()) is required here but I'm not familiar enough with the DAX.

In Stock SKU Count = 
CALCULATE([SKU Count], 
    FILTER(SkuHistoricalInventory, [Current OH (var)] > 5)

[amitchandak · Accepted solution]

@tdhlonghorn , Try a measure like

Sumx(Filter(Summarize( Table, Table[Department], Table[SKU], "_measure", [Current OH (var)]), [Current OH (var)]>5), [SKU Count])

[tdhlonghorn]

Amazing, that did the trick - thank you!