Tricky Measure Similar to SUMIF in Excel

[Anonymous]

Hi All,

I attached my PBI test file to help with my issue. Can any of  you help? I have tried just about everything that I can think of and keep striking out. Below is an explaination of what I am trying to accomplish as well as the PBI test file link. 

Power BI Test File 

[lbendlin]

Does ABC always consist of 123,214, and 564, and always in these ratios? or does any of that change?

[Anonymous]

Hello! ABC will always consist of 123, 214 & 564 at the ratios above unless the formula changes (which can happen, but does not happen often). I plan to refresh the report monthly to pull in the active recipe for each item (there will only ever be 1 ABC, but the 123,214,564 and ratios could change month to month). 

I appreciate you taking a look at this!!

[lbendlin]

If you only update the recipes monthly then I would recommend exploding the ingedients out, such that CBE would be 

568 0.1

659 0.1

123 0.8*0.1

214 0.8*0.2

564 0.8*0.7

You could still report it by ABC if needed but this approach would greatly simplify the computation.

Let me know if this is acceptable.

[Anonymous]

Good morning, I see what you are saying here. Totally makes sense to me. With my sample data, do you know how I could totally expand out ABC like you did above so it is connected to CBE? I tried a couple things but it doesn't seem to be computing correctly. My data source will only show ABC in the formula for CBE - I don't have the ability to export the source data so that it is fully expanded.