Trouble with filtering while aggregating to a higher level

Hi all,

I have a little challenge where I do not find a solution for and where I hope you have an advise for me.

What I want to achieve: I would like to create a report showing purchase price variance compared to a reference price valid at a selected date (dynamic). E.g. purchase price variance compared to reference prices valid at 01.01.

What is the challenge: If I have my report on the lowest level (i.e. material level) it seems to work perfectly. However ,when I want to aggregate that report to a higher level, i.e. aggregate purchase price variance per vendor I am facing a problem which is linked to  my filtering. Namely I have integrated a filter (in the filter pane) making sure that only materials will be considered when there is a reference price available at the selected date. W/o that filter the analysis would return odd figures. This filter works fine when having the report on the lowest level (material level), but it does not function when aggregating to vendor level. In this case the filter does not work. I.e. PBI also calculates a purchase price variance for material items where there is no reference price available at the selected date. As a consequence the the purchase price variance is overrated. As you can see in the screenshot below there is a material "D" which does not have a reference price. When having the report at the lowest level (material) the filter works fine and the it shows me 14% variance. But when I aggregate it and keep the filter on reference price (is not blank) it does not function as it considers material "D" in its computations.

Hence, do you see a way how my filtering (reference price is not blank) can also be considered correctly (i.e. disregarding material "D") when aggregating to vendor level?

Worth to be mentioned: column E-I are calculated measures

I could also share the underlying excel file, but not sure how I can insert it here (seems no button for this here)

Your help is much appreciated

Cheers

Harry