Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
BudMan512
Helper V
Helper V

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

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
 

 

BudMan512_2-1682520129653.png

 

BudMan512_1-1682519651565.png

 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

3 REPLIES 3
Greg_Deckler
Super User
Super User

@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

Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

@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   

@BudMan512 It's my pleasure!


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.