add measure to sum distinct product by week.number

[Roberto33]

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

[Roberto33]

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]

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]

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