cancel
Showing results for
Did you mean:
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.

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
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] )`

Best
Darek

Announcements