Forum Discussion
Creating multiple relationships in Power BI
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.
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.
- 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.
- 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.
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.
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.