Forum Discussion
Total not showing up
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 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.
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
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] ) )You are Magic Greg_Deckler ,
I really apreciate a lot your help and the time you took to check my model. ¡Gracias!
All is good now!
Big thanks!
Fernando
P.S. Simple is best, right?
calerof My pleasure! Please make sure to vote! https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e
The take-away here is to always SUMMARIZE by what is exactly in your table. Not sure why things were thrown off when using Costo table but it was.
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.