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

[Anonymous]

I have a disconnected table which has target percentages for gross margin (table 2). I have another table which has the raw data in which I calculate the margin percentages (table 1). 

PROBLEM = My Target Gross Margin IF statement (the output of either YES/NO listed on Table 1) is not calculating correctly in relation to the target percentages (the percentages listed in Table 2). 

- EXAMPLE: See line 1 of Table 1. Item Cost $8.93 -> The Target Gross Margin % = 71%. If the Margin %_purch does not exceed this percentage, the output should = NO. Currently, the output = YES

TARGET SOLUTION = The goal is to have the IF statement produce the correct output of either YES or NO true to the percentage targets listed in table 2. 

- EXAMPLE: See line 1 of Table 1. Item Cost $8.93 -> The Margin %_purch is < Target Gross Margin 71%. The output should = NO. 

Any ideas on where I am going wrong? My intuition is leading me to believe that my DAX syntax is wrong. 

Below are my DAX Syntax: 

MeetTargetGrossMargin? =

IF(

[Margin %_purch] >

SELECTEDVALUE('Cost Gross Margin%'[Gross Margin]),

"Yes", "No"

)

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

(Table 1 - Raw Data w/ Margin %_purch)

(Table 2 - Disconnected Table w/ Target Gross Margin Percentages)

[AlB · Accepted solution]

@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"
)

Code formatted with  

[AlB]

Hi @Anonymous

Is the first table that you are showing an actual table or a matrix visual? What are you trying to do with

SELECTEDVALUE('Cost Gross Margin%'[Gross Margin]) ? Get the gross margin for that price?  

Are [Margin %_purch] and MeetTargetGrossMargin? measures and columns?

[Anonymous]

@AlB thank you for your response. 

Table 1 is an actual table. The [Margin %_purch] measure is pulling information from Table 1. 

The goal with using "SELECTEDVALUE('Cost Gross Margin%'[Gross Margin])" is to have [Margin %_purch] measure compare against [Gross Margin]. Yes you are correct, compare the gross margin for that price + provide an output that will confirm "Yes" or "No". 

[Margin %_purch] = Measure

[Gross Margin] = Calculated Column

My initial measure for gross margin did not work because I was getting an error message that would say "a table of multiple values was supplied where a single value was expected". Below was my initial measure which I was trying to implement. 

Gross Margin = 
IF( [Margin %_purch] > FILTER('Cost Gross Margin%'[Gross Margin]), "Yes", "No")

What are your thoughts? Am I on the right track? How can I get this to calculate correctly to product a true output of YES or NO which will holds true? 

[AlB · Accepted solution]

@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"
)

Code formatted with  

[Anonymous]

@AlB ABSOLUTELY STUNNING! I have literally been working on this DAX syntax for 3 days and you were able to cook this up so fast! How on earth did you do that? 

So much gratitude for you! THANK YOU! 

[AlB]

@Anonymous

You're very welcome

SELECTEDVALUE( Table1[Column]) gives you the filtered value in the Column if it's only one. If there are multiple values, it will return a blank. You were using: 

SELECTEDVALUE('Cost Gross Margin%'[Gross Margin])

Since you had no filter on 'Cost Gross Margin%'[Gross Margin], that SELECTEDVALUE() would always return a blank, which functions pretty much as a zero in comparisons with numbers. That's why you got the "No" only with non-positive values of [Margin %_purch]

[Anonymous]

@AlB Thank you for the explanation. It is slowly starting to make sense. 

Question for you. After doing some QA on the results, I discovered that all of the YES outputs held true but the NO outputs was not as accurate. There are items which had the [Margin %_purch] >= [Gross Margin]. The anticipated output of these items would be YES but actual output was NO. 

I tried to play around with the DAX syntax but was not able to get the final output to be accurate. Any ideas or guidance you can provide? The syntax you provided was followed. 

Below is a screenshot of the sample output results that are not holding true. 

In a previous post, you requested to see the data table in which I am pulling the [Item Cost] and [PRICE]. Please see sample data below. 

Also as a reference, below is a screenshot of the [Gross Margin] 

[AlB]

@Anonymous

Can you share the pbix so that I can take a look?

[Anonymous]

@AlB thank you for your guidance! So much gratitude! 

The tab that I am referencing is "Duplicate of Page 1". Upon discovering the tab, you will notice that I have the NO filter selected along with a cost band selected. Some of the NO outputs are true but many of the NO outputs should truly have a YES as the 'PurchaseOrderTrans'[Margin %_purch] is > 'Cost Gross Margin %'[Gross Margin].

Please let me know your thoughts. 

[AlB · Accepted solution]

@Anonymous

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

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  

[Anonymous]

@AlB so much gratitude! Thank you soooo much!

Although my setup was a complete disaster, there are some minor details that needed to be addressed to finally get the correct output. I was somewhat on the right path but YOU my friend, was my guiding light! 

Best wishes and regards to you!

Anthony

[AlB]

@Anonymous

No worries mate. You're very welcome. It's always nice to come across grateful people.

It wasn't a disaster. Just needed some minor tweaking.

Cheers