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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
sistancy
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)Screenshot 2023-04-24 103314.png
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
lbendlin
Super User
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.

Thank you very much for your answer!
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😊

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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