CALCULATE SUM with FILTER

[Timoneedhelp]

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

[Timoneedhelp · Accepted solution]

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

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

CALCULATE(

    SUM(Product%),

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

[tamerj1]

HI @Timoneedhelp 
You may try

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

[Timoneedhelp]

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 😉

[tamerj1]

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

[Timoneedhelp]

@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

[tamerj1]

😅