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 = ...
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  

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  

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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!