cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver III

## sum of a product

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

1 ACCEPTED SOLUTION
Resolver III

Got it!

`SUMX(ALLSELECTED(Data[Material]),CALCULATE(sum(Data[Stock Target]),ALL(Data[Date]))/CALCULATE(COUNT(Data[Material]),ALL(Data[Date])))`
3 REPLIES 3
Resolver III

Got it!

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

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?

Resolver III

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors