Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
kelsen
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.

kelsen_0-1696453839681.png

 but in power bi desktop its not.

kelsen_1-1696454063896.png

 

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

2 REPLIES 2
kelsen
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] ).

lbendlin
Super User
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,

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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