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]
            )
    )
calerof's avatar
calerof
Icon for Impactful Individual rankImpactful 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
Icon for Community Champion rankCommunity Champion
3 years ago

calerof OK, so I see that there are additional tables involved. Is there any chance you can share the PBIX via OneDrive or Box or something? It's going to be extremely difficult to solve this otherwise. There is a lot going on here.