9. Creating Product HierarchyPower BI allows you to create Product Hierarchy for columns present in the same table. For a step by step guide on this: https://powerbitraining.com.au/data-model-common-issues-and-solution-9-power-bi-training-in-sydney-power-bi-training-in-melbourne/https://powerbitraining.com.au/data-model-common-issues-and-solution-10-power-bi-training-in-sydney-power-bi-training-in-melbourne/https://powerbitraining.com.au/data-model-common-issues-and-solution-11-power-bi-training-in-sydney-power-bi-training-in-melbourne/1. Load the Sales Target
2. Click on Month in the Sales Target Table and drag the cursor to Date column in the Date Table.
A one-to-many relationship is created. This is because for a given month, different targets are set for each product table.
3. Click on Product Type in the Sales Target Table and drag to the Product Table.
A many-to-many relationship is created. Each product is assigned multiple targets, one for each month.
4. Click on Table in the Visualization Pane.
5. Drag and drop the Date from the Date Table to Values.
6. Drag and drop the Product Type from the Product Table to the Values.
7. Drag the Quantity Sold from the Sales Table to the Values.
8. Drag and drop the Target Quantity from the Sales Target Table to the Values.
9. Press Ctrl+C to copy the Visual.
10. Press Ctrl+V to Paste the visual.
11. In the new table, Remove Product Type and Add Product from the Product Table.
Two table visuals have been created. Each of them shows a different granularity level. As the target has been defined for each Product Type and not Product, the values in the second visual is not incorrect however it is misleading. In the first visual we have 4 rows for January 1st, 2019. Whereas, in the second visual, we have 8 rows for the same date.
This issue will be address in the next blog.
It is often considered impossible to have more than one fact table in any data model. However, some business problems cannot be solved without multiple fact tables. One of such problems is adding a monthly target table. This blog provides a step by step guide on this problem.
Learning Power BI from Scratch? Get a complete, in-depth introduction to Microsoft Power BI tool by attending our Power BI Basic Training Course.
More Checkouts for Power BI Training in Australia.