sum of a product

[MagikJukas]

Hello,

I have a table containing:

• list of materials
• A column with values
• dates

the same material has the same value.

I want to calculate the sum of values groupped by materials, in other words avoiding summing twice the same material.

On top of that, I would like to display this measure in a chart at a constant.

I wrote this formula, but it does not aggregate correctly when selecting multiple materials.

M1 = CALCULATE(SUMX(ALLSELECTED(Data[Material]),SUM(Data[Stock Target])/COUNT(Data[Material])),ALL(Data[Date])

thanks

[MagikJukas · Accepted solution]

Got it!

SUMX(ALLSELECTED(Data[Material]),CALCULATE(sum(Data[Stock Target]),ALL(Data[Date]))/CALCULATE(COUNT(Data[Material]),ALL(Data[Date])))

[FreemanZ]

hi @MagikJukas 

unable to follow, you mentioned:

"same material has the same value"

and 

"avoiding summing twice the same material"

so why bother sum at all? Could you explain your expectation further?

[MagikJukas]

I want to group by material and then sum the total.

It is like counting and count distinct, I do not want to count more than once the same material.

Hopefully it is more clear now.