cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Wrong Total

How can i fix my total in a matrix.

The expresions are:

Estoque:=
VAR DATAINI=FIRSTDATE(ALL('Calendar'[Data]))
VAR DATAFIM=LASTDATE('Calendar'[Data])
VAR Estoque = CALCULATE(_Medidas[Saldos],
ALL(Saldos[Tipo]),
DATESBETWEEN('Calendar'[Data],DATAINI,DATAFIM))
RETURN
Estoque

Valor Estoque :=
VAR UltimoCusto = CALCULATE(
LASTNONBLANK(Saldos[Preco_Custo_Medio]; 0);
VALUES(Produtos[Nome_Produto]);
FILTER(
ALL(Saldos);
Saldos[Data_Movimento] <= MAX('Calendar'[Data])
);
ALL('Calendar')
)
RETURN

[Saldo_Retro]*UltimoCusto

13 REPLIES 13
Super User

Hi,

Please describe the question, share data in a format that can be pasted in an MS Excel file and show the expected result.  If possible, could you share the column names in English language?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

I changed the column and measurement names to English.

Frequent Visitor

I need to calculate the inventory on the selected date. Physical inventory and inventory value.

I can calculate the measurements, but the totals are not adding up

Thanks,

Super User

Hi,

You may downlaod my PBI file from here.  I have also simplified your measures

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

"Good job, But I need to check the balances from previous periods as well. For example: Click on the year 2022, month of August, and be able to see the physical and financial retroactive balance.

However, with these measures, when I filter a year and month, the data only calculates the information for the selected period."

Super User

Thank you.  That is a new requirement.  Not in any previous post have you mentioend that.  Explain your new requirement very clearly.  Show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Frequent Visitor

Yes, man! You are correct.

But my real intention is to get the stock balance from a previous date as well. For example, if the user selects March 2022, they should be able to see the exact stock balance from March 2022 of the previous year.

You get it ?

Here is the link to my model again. I have added some slicers for the year and month.

Frequent Visitor

Here is the link to my model again. I have added some slicers for the year and month.

Super User

Hi,

Use this measure to get the stock value in the same month last year

``Inventory value in SPLY = CALCULATE([Inventory value],SAMEPERIODLASTYEAR('Calendar'[Data]))``

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

@IsmaelConsult 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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

I separated the measurements but it didn't work, here's the link to the file so you can analyze it better. I also changed the column and measurement names to English.

I need to calculate the inventory on the selected date. Physical inventory and inventory value.

I can calculate the measurements, but the totals are not adding up

Thanks,

Frequent Visitor

@Greg_Deckler
I try with this metric:

Valor Estoque =
VAR UltimoCusto = CALCULATE(
LASTNONBLANK(Saldos[Preco_Custo_Medio]; 0);
VALUES(Produtos[Nome_Produto]);
FILTER(
ALL(Saldos);
Saldos[Data_Movimento] = MAX( Saldos[Data_Movimento])
);
ALL('Calendar')
)
VAR _VlrEstoque = [Saldo_Retro]*UltimoCusto
VAR _Table = SUMMARIZE(VALUES(Produtos[Nome_Produto]);"_value";_VlrEstoque)
RETURN
IF(HASONEVALUE(Produtos[Nome_Produto]);_VlrEstoque;SUMX(_Table;_VlrEstoque))

But not works.

Super User

@IsmaelConsult Can't do it like that, that's not how VAR's work. Use 2 separate measures like in the example.

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...