Forum Discussion
How do you get your IF statement model calculation to work correctly?
Community ChampionAnonymous
: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]
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?
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
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" )