topic Re: I need the cost to only appear for some products and not others in DAX Commands and Tips

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

BudMan512 — Wed, 26 Apr 2023 14:53:27 GMT

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

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

Greg_Deckler — Wed, 26 Apr 2023 16:10:00 GMT

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

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

BudMan512 — Wed, 26 Apr 2023 19:49:24 GMT

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

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

Greg_Deckler — Wed, 26 Apr 2023 20:57:54 GMT

BudMan512 It's my pleasure!