Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Not applicable

Tricky Measure Similar to SUMIF in Excel

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 

Super User
Super User

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

Not applicable

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!!

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.

Not applicable

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. 

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.