Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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

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)

 

General Comment
Need Help
Tutorial Requests
  • AlB's avatar
    AlB
    7 years ago

    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's avatar
    AlB
    7 years ago

    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  

11 Replies

  • AlB's avatar
    AlB
    Community Champion
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        AlB
        Community Champion
        7 years ago

        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