How do you get your IF statement model calculation to work correctly?

Anonymous

Can you show me the actual structure of your Table1? What you show in your first screen capture looks like a  matrix visual. If so, what fields are you using in that visual?

If I understand correctly what you are showing, try this:

MeetTargetGrossMargin? =
IF (
    [Margin %_purch]
        > CALCULATE (
            VALUES ( Table2[Gross Margin] ),
            FILTER (
                Table2,
                Table2[Cost From] <= SELECTEDVALUE ( Table1[PRICE] )
                    && Table2[Cost To] >= SELECTEDVALUE ( Table1[PRICE] )
            )
        ),
    "Yes",
    "No"
)

Anonymous

I see there are quite a few issues. You marked it as solved but it's not working at all :smileyvery-happy: :smileyvery-happy:

1. First of all, the code I provided earlier was meant to be a measure, since you were showing a visual and were already using another measure to calculate the margin

2. I do not understand how the margin is calculated for each item. The code for your measure is:

Margin %_purch = DIVIDE(SUM(PurchaseOrderTrans[PRICE]) - SUM(PurchaseOrderTrans[Item Cost]); SUM(PurchaseOrderTrans[PRICE]))

I'm at a loss there, given the set-up you have in your visual. What are you trying to do? How would be the margin calculated conceptually? Why the SUM( )s?? Why does it have to be a measure?

3. I would just keep the calculation of the margin as a calculated column in your table, i.e. calculated per row in your table (don't know if that's the requirement):

MarginAsCOLUMN =
DIVIDE (
    PurchaseOrderTrans[PRICE] - PurchaseOrderTrans[Item Cost];
    PurchaseOrderTrans[PRICE]
)

4. And then another column in your table for the check:

MeetTargetGrossMargin? =
IF (
    PurchaseOrderTrans[MarginAsCOLUMN]
        > CALCULATE (
            VALUES ( 'Cost Gross Margin%'[Gross Margin] ),
            FILTER (
                ALL ( 'Cost Gross Margin%' ),
                'Cost Gross Margin%'[Cost From] <= Table1[PRICE]
                    && 'Cost Gross Margin%'[Cost To] >= Table1[PRICE]
            )
        ),
    "Yes",
    "No"
)

Code formatted with