Forum Discussion
Smart IF
Hi,
I'm looking for a smart way, using a measure, to assign a discount value against a customer.
I have 2 tables Transactions and DiscountCriteria. I would like to SUM the Tons for each Company, and then depending on the Total SUM, apply a discount e.g. Company A have 1.8 Tons so they would get a 0.03 discount (discounts are all greater than).
I know I could do this by writing a large IF measure, but I'm wondering if there was a better way to do this.
Transactions:
| Company | Date | Tons | Value |
| A | 01/01/2016 | 0.5 | 35 |
| A | 01/03/2016 | 1.3 | 100 |
| B | 01/03/2015 | 3 | 256 |
| C | 01/04/2015 | 4 | 354 |
| D | 01/03/2015 | 1.1 | 123 |
DiscountCriteria:
| Tons | Discount |
| 0.2 | 0.01 |
| 0.5 | 0.02 |
| 1 | 0.03 |
| 3 | 0.04 |
| 5 | 0.05 |
Thanks,
Jake
5 Replies
The smarter IF is called SWITCH.
Thanks Greg_Deckler, I've gone with the tried and tested SWITCH(TRUE()) formula, how could I have forgot about this beauty:
Discount - Volume =
SWITCH (
TRUE (),
AND ( SUM ( SAGE_Extract[Tons] ) > 2, SUM ( SAGE_Extract[Tons] ) <= 5 ), 0.01,
AND ( SUM ( SAGE_Extract[Tons] ) > 5, SUM ( SAGE_Extract[Tons] ) <= 9 ), 0.02,
AND ( SUM ( SAGE_Extract[Tons] ) > 9, SUM ( SAGE_Extract[Tons] ) <= 12), 0.03,
AND ( SUM ( SAGE_Extract[Tons] ) > 12, SUM ( SAGE_Extract[Tons] ) <= 14), 0.04,
SUM ( SAGE_Extract[Tons] ) > 14, 0.05
)This has raised the next issue for me. Every row in the Transactions table has a currency against it e.g. "USD" or "GBP". I would like to add something like this to my measure:
If the currency = "USD" then multiply the discount by my Currency Conversion Rate measure.
Any thoughts?
You can create another measure
AdjustedDiscunt = if (currency = "USD", [Discount - Volume ]*[Currency Conversion Rate],[Discount - Volume ] )
Just add table names to above formula
