Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
IsmaelConsult
Frequent Visitor

Wrong Total

How can i fix my total in a matrix.

IsmaelConsult_1-1679669392025.png

 

 

 

The expresions are:


Saldos:= CALCULATE(SUM(Saldos[Entrada])-SUM(Saldos[Saida]))


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
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

I changed the column and measurement names to English.


https://drive.google.com/file/d/1wdZ1YnBT2BqekXoaPc3yZhkpth_X5i83/view?usp=share_link 

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,

Hi,

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

Hope this helps.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

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
https://www.linkedin.com/in/excelenthusiasts/


Yes, man! You are correct.
I really appreciate your help.

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.

Thanks for your time.

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

Thanks for your time.

 

https://drive.google.com/file/d/1SRLaszxsVbKNi70vtu7A9QqzRsVd9tr4/view?usp=share_link

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.

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

https://drive.google.com/file/d/1wdZ1YnBT2BqekXoaPc3yZhkpth_X5i83/view?usp=share_link 

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,

@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)) 
IsmaelConsult_0-1679705524535.png

 


But not works.


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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.