Trouble with filtering while aggregating to a higher level

[Harry1980]

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

Spoiler

[Anonymous]

Hi @Harry1980 ,

You can refer the following links to resolve the wrong total values problem. 

Use IsInScope to get the right hierarchy level in DAX

Clever Hierarchy Handling in DAX

Power BI: Totals Incorrect

Fixing Incorrect Totals Using DAX Measures In Power BI

If the above ones can't help you get the correct results, please provide some sample data (exclude sensitive data) and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file with me. You can refer the following thread to upload your file in the community. Thank you.

How to upload PBI in Community

Best Regards