Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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
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] ).
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,
User | Count |
---|---|
81 | |
75 | |
73 | |
42 | |
36 |
User | Count |
---|---|
114 | |
56 | |
51 | |
42 | |
42 |