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 Excel the correct result is $154,150, as follows:

 

Correct total in MS Excel

 

In Power BI shows like this:

Total not showing in the table visual

 

The base measure is the following:

 

Costo de Venta de Stock = 
SWITCH(
    TRUE(),
    AND( [Saldo Unidades Inicio de Mes] >= [Cantidad Total],[Cantidad Total] > 0), [Costo de Venta],
    AND( [Cantidad Total] > [Saldo Unidades Inicio de Mes], [Saldo Unidades Inicio de Mes] > 0), [Saldo Unidades Inicio de Mes]*([Costo de Venta]/[Cantidad Total]),
    BLANK()
)

 

 

 

The total measure is the following:

 

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

 

 

This is my data.

MS Excel file

 

I don't know what I'm doing wrong.

Thanks,

Fernando

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]
            )
    )

11 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    calerof I'll take a closer look at this but in the mean time:

    First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

    This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

    Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
    https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

    Also: https://youtu.be/uXRriTN0cfY
    And: https://youtu.be/n4TYhF2ARe8

  • Greg_Deckler's avatar
    Greg_Deckler
    Community 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
      Impactful 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
        Community 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.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community 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
      Impactful 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