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, taking a closer look at this, is "Costa de Venta de Stock" really a measure? The reason I ask is that there are no aggregations around what appear to be columns unless those are also measures, like "Candidad Total". I'm having some trouble recreating this as the names of the columns, etc are different.

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

    Hi Greg_Deckler ,

    Yes, all columns are measures, not actual columns from the table. The Inventory table has the following columns:

    1. Item ID
    2. Warehouse ID
    3. Inputs in Units
    4. Outputs in Units
    5. Inputs in money
    6. Outputs in money
    7. Date

    Inventory table

    The measure Saldo Unidades (Inventory Balance Units) is:

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

    The whole purpose is to know the COGS only of those items with beginning inventory. If they didn't have stock at the beginning of the month, then if it got purchased and then sold, it's not part of the KPI, only items sold that were in stock. Finally, the KPI will be an index of COGS with stock divided by total inventory.

     

    The base measure, Costo de Ventas Stock (COGS from Stock) is just checking if the item sold this month had beginning inventory and then bringing in the COGS.

    Many thanks!

    F

    • Greg_Deckler's avatar
      Greg_Deckler
      Icon for Community Champion rankCommunity Champion
      3 years ago

      calerof OK, I'll need all of the measure formulas involved then. It's likely something buried in one of those formulas and quite likely involves CALCULATE perhaps.

      • 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