Nested ifs and filter

[DRossi]

Hello,

I am trying to create a custom column to understand the fee due. I currently have the below but I can't get it to work. I also need to add a filter but I don't know where to start with that:

Fee = IF(NOT(LH_Jobs[TradeId] IN {7}) && LH_Jobs[BilledTotal] <= 500.00,  LH_Jobs[BilledTotal] * 0.2,
IF(AND(NOT(LH_Jobs[TradeId] IN {7}) && LH[BilledTotal] > 500.00, LH_Jobs[BilledTotal] <= 1500.00), LH_Jobs[BilledTotal] * 0.15,
 IF(NOT(LH_Jobs[TradeId] IN {7}) && LH[BilledTotal] > 1500.00,  LH_Jobs[BilledTotal] * 0.1
, Blank()
)))

  
What Im trying to do is:

- Check that the row has a LH_Status = Closed (this is the bit I don't know how to do), if it's not closed then Blank()

- If the TradeId is = 7 then Blank() ( I want to calculate the below for all other TradeIds
- If billed total is < = 500 then multiply the rows billedTotal by 0.2
If billed total is > 500 and <= 1500 then multiply the rows billedTotal by 0.15
If billed total is > 1500 then multiply the rows billedTotal by 0.1

[DRossi]

Awesome! I just had to amend it for the between values.

[latimeria · Accepted solution]

Try this

SWITCH(
    TRUE(),
    LH_Status <> "Closed", BLANK(),
    TradeId = 7, blank(),
    LH_Jobs[BilledTotal] < = 500, LH_Jobs[BilledTotal] * 0.2,
    LH_Jobs[BilledTotal] <= 1500.00, LH_Jobs[BilledTotal] * 0.15,
    LH_Jobs[BilledTotal] * 0.1
    )