Create Measure With Filter Conditions

[rbowen]

I'm trying to create a measure that uses some conditions for the final result that will included in a table visual. I have 3 previously created measures, one for Sales, one for Costs and one for Rebates. I also have a column in the data for various categories - A, B, C and D. I need to create a measure that calculates net margin for each of the categories, but when the category is A, I need to include rebates, otherwise just use Sales and Costs.  So, something like this:

NetMargin = IF([Category] = "A", [TotalSales] -  [TotalCosts] + [TotalRebates], [TotalSales] - [TotalCosts])

Where I'm running into trouble is the IF([Category] = "A" piece. I get a red underline under the [Category] part with an error message saying "Cannot find name [Category]".  I'm positive I have the syntax wrong but not sure how to correct it.  Any ideas what I'm missing?

Thank you.

[Anonymous]

Hi @rbowen 

Can you tell me if your problem is solved? If yes, please accept it as solution.

Regards,

Nono Chen

[Anonymous]

Hi @rbowen 

@Kaviraj11 Thank you very much for your prompt reply. Allow me to share some content here.

Measure = 
IF(
    SELECTEDVALUE('Table'[Category]) = "A", // YourTableName[Category] = "A", 
    [TotalSales] -  [TotalCosts] + [TotalRebates], 
    [TotalSales] - [TotalCosts]
)

Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rbowen · Accepted solution]

Thank you for the reply Nono. I gave up on this approach and went a different direction by restructuring some of the data and altering some of the relationships. 

[Kaviraj11]

Hi,

Try this: 

IF(

SELECTEDVALUE([Category]) = "A", [TotalSales] -  [TotalCosts] + [TotalRebates], [TotalSales] - [TotalCosts])

[rbowen]

Thanks Kaviraj11, but this doesn't work either. I still get the same red underline under [Category] with the same error message. I've also tried using the aggregator SUM but same problem. Seems to be related to using a column name with the IF statement in combination with the measures. The syntax is almost certainly incorrect but I'm not sure what the correct syntax should be.