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

Anonymous

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

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

    Anonymous

     

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

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