I need the cost to only appear for some products and not others

[BudMan512]

I would appreciate your help with this.

Below is a screenshot of a report I am working on.  I only have the cost for products beginning with "L",  yet every product has the "Avg Cost 1" and "Avg Cost 2" populated with a value.  I would like any product beginning with some value other than "L" to be blank or have "0" if needed.  Avg Cost 1 and Avg Cost 2 are for different date ranges.  I have added the "ADJ COST 1" measure to try and limit where the cost shows.  Here is the latest iteration of DAX I have tried to accomplish my goal.

ADJ COST 1 = CALCULATE([Avg Cost 1], CONTAINSROW( {"L001", "L002", "L005", "L007", "L009", "L013", "L017"},Sales[CATEGORY]))

But as you can see in the below screenshot all products still have a value for cost in the "ADJ COST 1" column.

Here is the DAX I am using for Average Cost.

Avg Cost 1 = AVERAGE(Cost[COST])

Avg Cost 2 = CALCULATE(AVERAGE(Cost[COST]),ALL(Date1),USERELATIONSHIP(Date1[Date1],Date2[Date2]))

Could someone kindly assist in helping me with the DAX I need for the Avg Cost of both columns and only for products beginning with "L"? I have included screenshots of the report and model views.

Thanks much,

Bud

[Greg_Deckler · Accepted solution]

@BudMan512 Try:

ADJ COST 1 = 
  VAR __Category = MAX('Sales'[CATEGORY])
  VAR __First = LEFT(__Category, 1)
  VAR __Result = IF( __First = "L", [Avg Cost 1], BLANK())
RETURN
  __Result

[BudMan512]

@Greg_Deckler 

Greg,

Thanks so much for taking the time to answer my post.  The solution worked perfectly.  In fact I was able to modify that measure for 8 other columns.  It cleaned up my report nicely.

I have two of your books and read them as I have time.  I am an old man (72) in a young mans game but still enjoy it for the most part except when I run into a problem like this. Thankfully there are people like you who are happy to help.   Best Regards, Bud   

[Greg_Deckler]

@BudMan512 It's my pleasure!