cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Roberto33
Regular Visitor

add measure to sum distinct product by week.number

Hello, I'm newby in DAX.  i need to ask for favour some experts because I dot't get how to sum it.

Thanks in advanced.

 

I try to sum value from product by weeks

My measure sum all not filtered by 

 
Qty total for Product (code) =
CALCULATE(SUM('Sheet'[Qty]);ALLSELECTED('Sheet'[My_date_column]);'Sheet'[Column_with_nr_week])
 
How to change this formula to get  tab like this.
Product (code) / 15 / 16/ 17 / 18 / 19 / 20 / 21 / 22 / 23
aaab                / 400 / 500 / 200 / 700 / 900 / 1000 / 300 / 2200 
 
not like this 
Product (code) / 15 / 16/ 17 / 18 / 19 / 20 / 21 / 22 / 23
aaab                / 6200  / 6200 / 6200 / 6200 / 6200 / 6200 / 6200 / 6200
 
 
for example my row data it's look like this:
Product (code) / Qty / My_date_column / Column_with_nr_week
aaab / 111 / 2019-08-06 / 32
aaab / 113 / 2019-08-05 / 32
 
 
 
 
3 REPLIES 3
Roberto33
Regular Visitor

Dear Derek you have right about my knowledge of Dax. I need to start learn:)

 

Meanwhile I solve my case in those formula

 

Measure =
CALCULATE (
SUM ( 'Sheet'[Qty] );
ALLEXCEPT (
'Sheet';
'Sheet'[NR_of_week];
'Sheet'[Material (code)]
)
)
 
regards,
R

 

Anonymous
Not applicable

OK. Good.

But be careful with ALLEXCEPT: if there is no DIRECT filter on NR_of_week and/or Material (code), it will remove ALL filtering from the EXPANDED version of Sheet (so, not only Sheet but all tables that are in a 1:many relationship with Sheet) and you might end up with something you won't be able to explain.

As long as you KNOW exactly what ALLEXCEPT in CALCULATE does, you're safe. And, of course, you should test.

Best
Darek
Anonymous
Not applicable

Mate, there is a good rule in DAX:

 

DO NOT USE A FUNCTION IN YOUR CODE IF YOU DON'T KNOW PRECISELY HOW IT WORKS.

 

Let this statement above sink in. If you don't stick to it, you'll be sorry rather sooner than later. Please, before you start using something in your DAX code read about it. Here's a good source: www.daxguide.com.

 

Do you have any idea how ALLSELECTED works? No? That's what I thought.

 

ALLSELECTED is a very complex DAX function that works with something that's called "shadow filter context." To use it correctly you have to know quite a few things. Once again, please read about it.

 

Your measure should in all likelihood be this:

Qty total for Product (code) = SUM( 'Sheet'[Qty] )

I'd also urge you to read about the proper dimensional design.

 

Best
Darek

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors