cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Impactful Individual

## 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(),
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

1 ACCEPTED SOLUTION
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
11 REPLIES 11
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Impactful Individual

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.

F

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Impactful Individual

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(
'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

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Impactful Individual

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(
'Calendar'[Date] <= EndOfMonthPrevious
)
RETURN
BeginningOfMonthBalance``````

Ending Inventory (units) / Saldo Unidades

``````Saldo Unidades =
CALCULATE(
'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,
)``````

And finally COGS from stock and Total COGS from stock.

Thank you Greg!

F

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Impactful Individual

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?

Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

@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.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Super User

@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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors