Forum Discussion

calerof's avatar
calerof
Impactful 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]
            )
    )
calerof's avatar
calerof
Impactful Individual
3 years ago

Hi Greg_Deckler ,

Here are my measures:

Beginning Inventory (units) /  Saldo Unidades Inicio de Mes; from the Inventory table, inputs minus outputs.

Saldo Unidades Inicio de Mes = 
VAR MaxDate = MAX('Calendar'[Date])
VAR EndOfMonthActual = ENDOFMONTH('Calendar'[Date])
VAR EndOfMonthPrevious = EOMONTH(EndOfMonthActual, - 1)
VAR BeginningOfMonthBalance =
CALCULATE(
    SUM(Inventory[ENTRADAS_UNIDADES]) - SUM(Inventory[SALIDAS_UNIDADES]),
    'Calendar'[Date] <= EndOfMonthPrevious
)
RETURN
    BeginningOfMonthBalance

 

Ending Inventory (units) / Saldo Unidades

Saldo Unidades = 
CALCULATE(
    SUM(Inventory[ENTRADAS_UNIDADES]) - SUM(Inventory[SALIDAS_UNIDADES]),
    'Calendar'[Date] <= MAX('Calendar'[Date])
)

 

Units Sold / Cantidad Total; from the Sales table, invoices minus credit memos (I know I should do this in Power Query).

Cantidad Total = 
CALCULATE(
    [Total Quantity],
    FILTER(
        Ventas,
        Ventas[TIPO_DOCTO] = "F"
    )
) -
CALCULATE(
    [Total Quantity],
    FILTER(
    Ventas,
    Ventas[TIPO_DOCTO] = "D"
    )
)

 

COGS / Costo de Venta; from Material Master, unit cost times units sold.

Costo de Venta = 
SUMX(
    ARTICULOS,
    [Cantidad Total] * ARTICULOS[Unit Cost]
)

 

And finally COGS from stock and Total COGS from stock.

Thank you Greg!

F

 

Greg_Deckler's avatar
Greg_Deckler
Community Champion
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]
            )
    )