Creating multiple relationships in Power BI

[yacoubi]

Hi. I'm trying to create a dashboard for product/intermediate demand forecast. I have 4 tables:

1. Product
   • PID: Product ID (primary key)
   • Product name
   • Product PPC: a serial number from another system
   • Product category
2. Intermediate
   • IID: Intermediate ID (primary key)
   • Intermediate name
   • Intermediate PPC: a serial number from another system
   • Intermediate Cost-L: the cost of 1 liter for this intermediate
3. Product-Intermediate conjunction table (something like a recipe, each product has many intermediates and each intermediate can be in many products)
   
   • PROINT_ID: (Primary key)
   • PID: (foreign key)
   • IID: (foreign key)
   • Percentage: percentage of intermediate (out of 100%)
   • Cost Percent: calculated column = Percentage x Intermediate Cost-L
4. FORECAST used to forecast future orders of products
   • FID: (primary key)
   • PID: (foreign key)
   • Date: date of future order
   • Volume: volume of future order in liters

Below is the relationship schema in Power BI:

The chart/table works perfectly when showing forecast volumes for each product by month. The issue is when I try to list forecast volumes by month for each intermediate. I have attached a snap shot of the Excel file which is used as the data source:

I would really appreciate the help.

[yacoubi · Accepted solution]

I've finally found the answer: Below is the schema:

Schema

I've created two meausres. The first one in the "PRODUCT" table named "Product Volume":

Product Volume = CALCULATE(SUM(FORECAST[Volume]))

The 2nd is in the "RECIPE" table named "Intermediate Volume":

Intermediate Volume =

IF (

HASONEVALUE ( INTERMEDIATE[IID] ),

SUMX (

RECIPE,

RECIPE[Percent] * 'PRODUCT'[Product Volume]

),

BLANK()

)

And this worked perfectly.

[edhans]

You didn't say exactly wasn't working, just that it doesn't work. I suspect the issue though is the way your filters flow in the tables.

1. Make sure in any visual you use, you are using the PID from the Product table, not the Forecast table. The PID in the forecast table will not filter back to the intermediate tables.
2. The IID field in Intermediate won't filter anything by default either.

You could turn on a bunch of bi-directional filtering (you have it between Product and Product_Intermediate, but that probably shoudn't be there as bi-directional filtering can create issues if you don't understand how the data flows and what the other implications are. You can read more about that here, including the problems with bi-directional filtering.

usually I prefer to have measures turn on bi-directional filtering on as needed, but that doesn't work with slicers and such, so there is a need for bi-directional fitlering sometimes. To turn it on for calculations, you would use a measure like:

Measure =
CALCULATE (
    SUM ( Sales[SalesAmount] ),
    CROSSFILTER ( Item[ItemNumber], Sales[ItemNumber], BOTH )
)

This will cause the sales table to filter the item table (which may in turn filter a forecast table on the other side of the item table) for that measure only.

[yacoubi]

Thank you @edhans for the quick reply.

Maybe I was not clear enough describing the issue. I'm trying to display a chart or a table showing total intermediate forecast volumes by month. An example is:

Product 1 forecast volume for December is 54,330 liters

Product 1 has 4 intermediates with the following ratios:

• Intermediate 1 @ 25% = total volume of 13,583 liters
• Intermediate 4 @ 15% = total volume of 8,150 liters
• Intermediate 5 @ 40% = total volume of 21,732 liters
• Intermediate 6 @ 20% = total volume of 10,866 liters

It was simple creating a bar chart displaying forecast volumes by month for each PRODUCT but not intermediate.

[yacoubi]

I have been really struggling with this for the past week. Let me explain what is it that I'm trying to do and maybe you can help:

• A forecast is made for 1 product only. It has a date and volume to be ordered
• A product can have many forecasts
• Each product is made out of many intermediates (ingredients) using percentages totalling to 100%:
  • Example: product A = 15% intermediate 1 + 45% intermediate 2 + 40% intermediate 3

How can I create a table or chart showing the following:

monthly volumes for each intermediate (intermediate volume = product volume x intermediate percent for that product)

Below is a snapshot of my current Power BI schema:

I would really appreciate the help.