Forum Discussion

rbowen's avatar
rbowen
Helper III
2 years ago
Solved

Create Measure With Filter Conditions

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.

Need Help
  • rbowen's avatar
    rbowen
    2 years ago

    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. 

5 Replies

  • Kaviraj11's avatar
    Kaviraj11
    Solution Sage
    2 years ago

    Hi,

     

    Try this: 

    IF(

    SELECTEDVALUE([Category]) = "A", [TotalSales] -  [TotalCosts] + [TotalRebates], [TotalSales] - [TotalCosts])
    • rbowen's avatar
      rbowen
      Helper III
      2 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      rbowen
      Helper III
      2 years ago

      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. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi rbowen 

     

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

     

    Regards,

    Nono Chen