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

Frequent Visitor

## Total calculation

I need some help with this measure.
The Goal is calculate material consumption by product and product color for the lowest inventory material available and multiply by total sales, this is acomplished by "consumo media venda" column.
then calculate total material consumption by product and product color for all selected products, column "consumo total".
Using dax studio I created this measure which is exactely what I needed but when sumx is used on this table to sum "consumo total" and I put it on a visual by product description and color this measure is not aggregating materials consumption by all products.

``````FILTER (
SUMMARIZE (
FILTER ( fFichaConsumo, RELATED ( dMaterial[GRUPO] ) = "TECIDO" ),
dProduto[DESCRICAO],
dProduto[COR],
dMaterial[CHAVE_MATERIAL],
"consumo", CALCULATE ( AVERAGE ( fFichaConsumo[QT_CONSUMO] ) ),
"estoque",
CALCULATE (
SUM ( fEstoqueMP[QT ESTOQUE] ) - SUM ( fBaixaConsumo[QT_SALDO] ),
dDeposito[CODIGO DEPOSITO] IN { "0000", "0009", "0017" }
),
"menor estoque",
CALCULATE (
MINX (
FILTER ( fFichaConsumo, RELATED ( dMaterial[GRUPO] ) = "TECIDO" ),
CALCULATE (
SUM ( fEstoqueMP[QT ESTOQUE] ) - SUM ( fBaixaConsumo[QT_SALDO] ),
dDeposito[CODIGO DEPOSITO] IN { "0000", "0009", "0017" }
)
),
ALLSELECTED ( dMaterial[CHAVE_MATERIAL] )
),
"media venda", CALCULATE ( SUM ( fVendas[QT VENDIDA] ) / diasUteisPeriodo ),
"consumo media venda",
CALCULATE (
AVERAGE ( fFichaConsumo[QT_CONSUMO] ) * SUM ( fVendas[QT VENDIDA] ) / diasUteisPeriodo
),
"consumo total",
CALCULATE (
SUMX (
SUMMARIZE (
FILTER ( fFichaConsumo, RELATED ( dMaterial[GRUPO] ) = "TECIDO" ),
dProduto[DESCRICAO],
dProduto[COR]
),
CALCULATE (
AVERAGE ( fFichaConsumo[QT_CONSUMO] ) * SUM ( fVendas[QT VENDIDA] ) / diasUteisPeriodo
)
),
ALLSELECTED ( dProduto[DESCRICAO], dProduto[COR] )
)
),
[estoque] = [menor estoque]
)``````

This is the result from dax studio, although product TOP BOJO LISO 121006 isn't showing, both products has chave_material 0101038214 and "consumo total" is aggregating "consumo media venda" as expected.

but in power bi desktop its not.

pbix file: https://we.tl/t-TSpO2Rjh9T

2 REPLIES 2
Frequent Visitor

Hi @lbendlin, the issue is that "consumo total" isn't summing all selected ( produto[descricao] and produto[cor] ) whenever I use it on a visual where it has this same columns, whereas the same measure sum correctly once evaluated as table expression on dax studio.

Its returning the same value as "consumo media venda" that does not sum all selected ( produto[descricao] and produto[cor] ).

Super User

Kudos for already simplifying your sample data somewhat, but it is still too much.  See if you can reduce it more , just to the essence of your issue,

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