Create a calculated column from multi layer related tables

[jeroenbenda]

I have run into something that I cannot figure out how to do.

What I have:

- table RATES, which lists the different rate versions (2015, 16 etc)

- table BUDGET, which lists different versions of the budget, id, ratesId (foreign key to RATES) and name (and other stuff)

- table CATALOG, which lists the products, id (and other stuff)

- table BUDGET_ITEMS, which lists different counts per item per customer, for this question, budgetId (foreign key to BUDGET), catalogId (foreign key to CATALOG), count (the number of products in budget) and other stuff

- table RATE, which has the rate of a product for the different rates, id, ratesId (foreign key to RATES), catalogId (foreign key to CATALOG) and rate, the rate per item (and other stuff)

What I want is a pie chart of the cost of the budget items. Slicers of course to select the proper BUDGET and CUSTOMER (not in the list, but relatively simple). To get that I need a calculated column which does:

[BUDGET_ITEMS].count * [RATE].rate using the following links

[BUDGET_ITEMS].budgetId to [BUDGET].ratesId and combine that with [BUDGET_ITEMS].catalogId as a double key to [RATE].ratesId and [RATE].catalogId to get [RATE].rate.

The data I have had for a period of a few years and now I want to visualize it using Power BI.

I hope the description is clear.

[v-ljerr-msft]

Hi @jeroenbenda,

Could you post some sample data with your expected result? So that we could better assist with this issue. It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.

Regards