Forum Discussion

calerof's avatar
calerof
Icon for Impactful Individual rankImpactful Individual
3 years ago
Solved

Total not showing up

Hi Community, I'm struggling with a total measure (m_Total Costo de Ventas Stock) not showing at all in a table visual. I made a calculation that's working fine and now I just need the total. In MS ...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    calerof OK, I believe this solves it:

    m_Total Costo de Ventas Stock = 
VAR __table =
    SUMMARIZE (
        ARTICULOS,
        ARTICULOS[Artículo],
        "__value", [Costo de Venta de Stock]
    )
RETURN
    IF (
        HASONEVALUE ( Costos[ARTICULO_ID] ),
        [Costo de Venta de Stock],
        SUMX (
            __table,
            [__value]
            )
    )
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
3 years ago

calerof OK, I was able to get the correct answer. See PBIX attached below signature. I had to create these two measures:

Costo de Venta de Stock = 
SWITCH(
    TRUE(),
    AND( MAX('Table'[Beginning Inventory (Units)]) >= MAX('Table'[Units Sold]),MAX('Table'[Units Sold]) > 0), MAX('Table'[COGS]),
    AND( MAX('Table'[Units Sold]) > MAX('Table'[Beginning Inventory (Units)]), MAX('Table'[Beginning Inventory (Units)]) > 0), MAX('Table'[Beginning Inventory (Units)])*(MAX('Table'[COGS])/MAX('Table'[Units Sold])),
    BLANK()
)


m_Total Costo de Ventas Stock = 
VAR __table =
    SUMMARIZE('Table','Table'[Item],"__value",[Costo de Venta de Stock])
RETURN
    IF ( HASONEVALUE('Table'[Item]), [Costo de Venta de Stock], SUMX(__table,[__value]))
calerof's avatar
calerof
Icon for Impactful Individual rankImpactful Individual
3 years ago

Hi Greg_Deckler ,

As I'm using measures I can't use MAX as you showed.

I changed the Total COGS from stock measure replacing the Inventory table for the Cost table:

m_Total Costo de Ventas Stock = 
VAR __table =
    SUMMARIZE (
        Costos,
        Costos[ARTICULO_ID],
        "__value", [Costo de Venta de Stock]
    )
RETURN
    IF (
        HASONEVALUE ( Costos[ARTICULO_ID] ),
        [Costo de Venta de Stock],
        SUMX (
            __table,
            [__value]
            )
    )

And now I'm seeing a value, but still wrong:

I left the base measure COGS from stock as I had it before, as I can't use MAX with measures.

I'm still thinking.

I appreciate your help.

F