cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Problem with relations of tables and trying to do a measure

Hello everyone

My Power BI dashboard is intended to show costs of products monthly in the past by having monthly prices of the articles with are needed to build the product and the quantity of articles which are processed in the product. Therefore I need to calculate the total costs by multiplying the quantity of the articles for the selected product with the monthly prices for the articles. Thats why I thought to do it with the SUMX-Function like in the following:

Gesamtkosten = SUMX(Mengen, Mengen[Menge] * RELATED(EntpPreise[Preise]))
or translated
total costs = SUMX(tblQuantity, tblQuantity[Quantity] * RELATED(tblPrices[Prices]))

But I get a error message like the following:
"The column "tblPrices[Prices]" either does not exist or has no relation to a table that is available in the current context."

I think the problem is based on the relations between my tables but I don't know a better way to design it
I added a screenshot of the relations (It is written in German but relations between the tables are based on columns with the same names) (Translation of the columns: "[Mengen]" = quantity, "[Preise]" = prices)
I tried a n:m relation between "EntpPreise" and "Mengen" ("tblPrices" and "tblQuantity") but it doesnt work either.

I hope someone can help me out
Thanks!

Best regards
Marius
3 REPLIES 3
Super User
`` show costs of products monthly in the past by having monthly prices of the articles with are needed to build the product and the quantity of articles which are processed in the product. ``

Your EntpPreise table needs a Produkt column.

Frequent Visitor

But I don't think so, because articles are needed to build the product and the prices are only based on these articles and don't depend directly on the product. Therefore products are only connected trough the quantity of the articles they need.
My problem is that its just not possible to calculate between quantity and price. It seems to me that Power BI just can't use the article number of the selected row from table "prices" go through the table "article" and find the same article number in table "quantity", or at least I don't know how to command it properly. I tried it in Access, there it is no problem at works completely fine.
To evade this problem I created a table with a VLOOKUP and several queries so prices and quantity are in one table and i can just calculate the costs in a extra column. This leads to many rows in this table, and doesn't seem like a proper database solution. But works currently...
If someone has other ideas I would be glad😊

Super User

In general the data model needs to follow the business scenario, not the other way round.  If you have a working solution that may not 100% conform to the star/snowflake mantra then so be it.

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors