topic Re: add measure to sum distinct product by week.number in DAX Commands and Tips

add measure to sum distinct product by week.number

Roberto33 — Tue, 06 Aug 2019 07:49:28 GMT

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

Re: add measure to sum distinct product by week.number

Anonymous — Tue, 06 Aug 2019 09:53:59 GMT

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

Re: add measure to sum distinct product by week.number

Roberto33 — Tue, 06 Aug 2019 19:06:05 GMT

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,

Re: add measure to sum distinct product by week.number

Anonymous — Tue, 06 Aug 2019 20:06:22 GMT

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