topic Re: CALCULATE SUM with FILTER in DAX Commands and Tips

CALCULATE SUM with FILTER

Timoneedhelp — Wed, 13 Apr 2022 07:07:55 GMT

Good morning Community,

I hope you can help me. Here is the structure of the table.

Product Line	Product No.	Product Name	Material No.	Country	Calendar year	Product %
A	5555511111	Product A	1111	BR	2021	10
A	5555511111	Product A	2222	US	2021	20
B	5555511111	Product A	2222	US	2021	20
B	5555511111	Product A	3333	CA	2021	50

What I expect: The sum of Product/Country/Year in each line (see below).

Product Line	Product No.	Product Name	Material No.	Country	Calendar year	Product %	SUM
A	5555511111	Product A	1111	BR	2021	10	10
A	5555511111	Product A	2222	US	2021	20	40
B	5555511111	Product A	2222	US	2021	20	40
B	5555511111	Product A	3333	CA	2021	50	50

I tried the following:

Support = CONCATENATE(ProductNo), CONCATENATE(Country),(YEAR)

CALCULATE(

SUM(Product %),

ALLSELECTED(Support)

)

Result is the same as in Product%

Does anyone have an idea?

Timo

Re: CALCULATE SUM with FILTER

tamerj1 — Wed, 13 Apr 2022 07:48:28 GMT

HI Timoneedhelp
You may try

RT % = VAR CurrentValue = [Product %] RETURN SUMX ( FILTER ( ALL ( Table ), [Product %] <= CurrentValue ), [Product %] )

Re: CALCULATE SUM with FILTER

Timoneedhelp — Wed, 13 Apr 2022 09:06:15 GMT

Hi tamerj1 ,

Thanks for the quick reply. It calculates... for almost more than 30 minutes (more than 350k lines) and I am curious how long it takes and how the result is... I will report 😉

Re: CALCULATE SUM with FILTER

tamerj1 — Wed, 13 Apr 2022 09:07:37 GMT

😅

Re: CALCULATE SUM with FILTER

tamerj1 — Wed, 13 Apr 2022 09:17:39 GMT

Timoneedhelp
Can you please share a screenshot of your table visual and the mesures code?

Re: CALCULATE SUM with FILTER

Timoneedhelp — Wed, 13 Apr 2022 09:39:08 GMT

tamerj1 I will send you a screenshot when the calculation is done...

In the table visual it is possible to get a SUM via the "Support = CONCATENATE(ProductNo), CONCATENATE(Country),(YEAR)" column, but I need the SUM in a seperate table to create a map with volume.

So my next step is a lookupvalue to the following table

ProductNo

Country

Year

Support2 = CONCATENATE(ProductNo), CONCATENATE(Country),(YEAR)

Result of Calculation via Lookupvalue

Re: CALCULATE SUM with FILTER

Timoneedhelp — Wed, 13 Apr 2022 12:22:12 GMT

sometimes the easy way is the beste way....

Support = CONCATENATE(ProductNo), CONCATENATE(Country),(YEAR)

CALCULATE(

SUM(Product%),

ALLEXCEPT('Table','Table'[Support]
)