Showing results for 
Search instead for 
Did you mean: 
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


2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.