DAX - Need a bit of help with Calculated Measure IF THEN to get me started…

[Hardz]

Hi all,

I’ve read through a bunch of the examples on the forum but I’m struggling to relate it to my data and get my head around the logic.

I have three types of invoice and need to apply a calculation based on the invoice number.

• For invoices starting with CW… I want to multiply it by 3/11
• For invoices starting with MS… I want to multiply it by 4/11
• For invoices starting with PR… I want to calculate the Price minus Cost (from another linked table)

In my “v_rpt_Invoices” table I have an “Invoice No” that starts with either MS, CW or PR (e.g. MS12345, etc.) and an “Invoice_Amount”

In my “v_rpt_Product” table I have “Extended_Price_Amount” and “Extended_Cost”

This isn’t the actual calculation but what I am trying to do is apply the following logic (I think)…

IF (v_rpt_Invoices[Invoice No]) = CW* THEN (v_rpt_Invoices[MarginExGST]) = (v_rpt_Invoices[Invoice_Amount]) * (3/11)

IF (v_rpt_Invoices[Invoice No]) = MS* THEN (v_rpt_Invoices[MarginExGST]) = (v_rpt_Invoices[Invoice_Amount]) * (4/11)

IF (v_rpt_Invoices[Invoice No]) = PR* THEN (v_rpt_Invoices[MarginExGST]) = (v_rpt_Product[Extended_Price_Amount]) - (v_rpt_Product[Extended_Cost])

I’m hoping it should look something like the below (I just don’t know what to do about the IF, THEN and *Wildcard…

MarginExGST =

IF (v_rpt_Invoices[Invoice No]) = CW* THEN sum(v_rpt_Invoices[Invoice_Amount]) * (3/11)

IF (v_rpt_Invoices[Invoice No]) = MS* THEN sum(v_rpt_Invoices[Invoice_Amount]) * (4/11)

IF (v_rpt_Invoices[Invoice No]) = PR* THEN sum(v_rpt_Product[Extended_Price_Amount]) - sum(v_rpt_Product[Extended_Cost])

Any help would be greatly appreciated!

Cheers

Hardy

[Phil_Seamark · Accepted solution]

HI @Hardz

This might be close

MarginExGST = 
VAR InvoiceType = LEFT(MIN(v_rpt_Invoices[Invoice No]),2)
RETURN 
    SWITCH(
        TRUE() ,
        InvoiceType = "CW" , sum(v_rpt_Invoices[Invoice_Amount]) * (3/11) ,
        InvoiceType = "MS" , sum(v_rpt_Invoices[Invoice_Amount]) * (4/11) ,
        InvoiceType = "PR" , sum(v_rpt_Product[Extended_Price_Amount]) - sum(v_rpt_Product[Extended_Cost])
    )

[Hardz]

Brilliant, thanks Phil!