Subtotal question

[heatherrink]

Hi there

I have a question please -

I have the following data in powerpivot - Products prescribed by individual practices roll up to Regions in a hierarchy. I have inserted calculated columns to get the subtotals for all the products within a region through
=CALCULATE(SUM([prescriptions]),FILTER('data','data'[Regions]=EARLIER('data'[Regions])))

However is there a way to get the subtotal by individual product within the region? I tried
=CALCULATE(SUM([prescriptions]),(FILTER('data','data'[Regions]=EARLIER('data'[Regions])),(FILTER('data',data[Product]=EARLIER('data'[Product]))))), but didnt get any success.

Could you help me please?

Thanks very much!!

 

[scottsen]

This should work. If not Tianbas will fix you up. Brother loves calculated columns

=CALCULATE(SUM([prescriptions]),
   FILTER(data,
             data[Regions]=EARLIER(data[Regions])
             && data[Product]=EARLIER(data[Product])
             )
   )

 

[heatherrink]

Brilliant thanks works perfectly!!!