How can i get totals in tables as i need them?

[Anonymous]

Hi to everyone here,

I'm new to Power BI Desktop and i've created a quick measure to know each cost per workplace and the total sum of all workplace costs. 

                         PBIX file here

The first part works great, but on last row ("Total") instead of a sum of all workplace costs, seems like it took the sum of all unitary cost per workplace and then multiply that value by the total quantity.

I replicate the data and structure on a dummy and the error persists. Have anyone had a similar problem?

I would really appreciate any advice on how to show data right.

Thanks in andavce,

Pere Joan

 

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I could not open the link, because it asked to enter the password.

However, please try to write the below measure and put it into the visualization.

Costs Total = Sumx (Table Name, [Quantity Column] * [Cost Column])

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

I could not open the link, because it asked to enter the password.

However, please try to write the below measure and put it into the visualization.

Costs Total = Sumx (Table Name, [Quantity Column] * [Cost Column])

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[Anonymous]

I'm sorry!
It should work now, i forgot to select no restrictions in Google drive.

https://drive.google.com/file/d/1JQ4JXyZE2xrjR4DwPxNYRcQwsx5RkbEF/view?usp=sharing

That a good solution, but i was wondering if it's a way to get the totals right in the same table.

At least if it's possible, it would be great to know where's the limit. What operation can i do that is going to be well sumed. Even if it's none.

[Jihwan_Kim · Accepted solution]

Hi, @Anonymous 

Thank you for your feedback and for sharing the sample pbix file.

After seeing the relationships between tables, please try to write the below measure and put it into the visualization.

UnitaryCost X quantity V2 =
SUMX (
TimeLineQuantity,
TimeLineQuantity[quantity]
* LOOKUPVALUE (
OFs[UnitaryCost],
OFs[manufacturingReference], RELATED ( manufacturingReference[manufacturingReference] ),
OFs[workplace], RELATED ( workplace[workplace] )
)
)

The cost is coming from a different table, and it is related to other different tables.

So, in my opinion, lookupvalue function and related function has to be used together.

[Anonymous]

I have some problems with the relationships.

It's because of the relationships.

In the dummy the relation between TimeLineQuantity and ManifacturingOrder is "many to one", but in the Good Report is "many to many".

As soon as i changed in the dummy it failed like in the good one.

But, It doesn't let me change it.

 What can i do? 😞

 

 

 

[Jihwan_Kim]

Hi, @Anonymous 

In my pbix file, I can change it to manytomany, then I can also change it back to onetomany.

Perhaps, just close your pbix file and open it again?

[Anonymous]

Yes, that's because i share with you the dummy.
The real one has some confidential information and its larger (f.e. 9 million rows in the TimeLineQuantity table)

I'm now looking if the manifacturingOrder relation differs in any aspeckt.

Thanks for your support @Jihwan_Kim 🙂

[Anonymous]

We fix it!

It was taking null cells into acount. Once I filtered them, it worked like a charm :D!

Thanks a lot @Jihwan_Kim !