CALCULATE within the lower granularity

1
votes

I have a matrix that is using the fields "Product Group" and "Product" in rows.

I want to calculate the average price for the products that had sales last year. Sales LY is a calculated measure:

Sales LY = CALCULATE(SUM('Table'[Qty]);SAMEPERIODLASTYEAR('Calendar'[Date]))

And this is the measure I'm trying to find:

AvgPrice= CALCULATE(DIVIDE([Turnover];[Quantity]);FILTER('Table'; [Sales LY]>0))

This works fine on the lower granularity (Product), but on the higher granularity (Product group) the calculation is wrong because the Product group is adding ALL the products within that Product group and I only want the ones that had sales last year.

How do I tell DAX: "Use the lower granularity"?

1
powerbidax

1 Answers

0
votes

One option for situations like this is to make the higher granularities a sum of the lowest granularity values. Something like this:

GroupAvgPrice = CALCULATE( SUMX( VALUES( 'Table'[Product] ), [AvgPrice] ) )

When there is only one product in the evaluation context, this simply reduces to [AvgPrice], but should work at higher granularities as well.