Hi,
I am having an issue with my relationships in Power BI Desktop. I have three datasets and one lookup table for an enterprise-wide budget dashboard. The datasets and lookup table information is found below.
Datasets:
- Main Consulting Firm- "Administrative Contract"
- Key Column(s): Division, costs of administrative contract
- Small subscription based contracts-- "Other Contracts"
- Key Column(s): Division, costs of other contracts
- Enterprise-wide data -- "Aggregated Dataset"
- Key Column(s): Division, Line Item, Salary Costs, Travel Costs, Training Costs, Rent Costs
Lookup Table:
- Division
- Key Column(s): Division (basically each division in the enterprise)
Essentially each of the 3 datasets has a M:1 relationship with the lookup table in order to be able to look at the Costs of Administrative Contract, Other Contracts, and Salary/ Travel/ Training/ Rent Costs in one table. However, the "Aggregated Dataset" simply sums each of the columns and does not separate them by division when included in one table. See sample table below.
| Division | Administrative Contract Cost | Other Contracts | Salary Cost | Training Cost | Travel Cost | Rent Cost |
| | $1,950 | $2,100 | $2,000,000 | $4,500 | $2,000 | $10,000 |
| 1 | $10,000 | $4,000 | | | | |
| 2 | $5,000 | $1,000 | | | | |
| 3 | $3,500 | $1,600 | | | | |
| 4 | $400 | $1,100 | | | | |
| 5 | $7,000 | $2,300 | | | | |
| Total | $27,850 | $12,100 | $2,000,000 | $4,500 | $2,000 | $10,000 |
How do I get the table to disperse the Salary/ Training / Travel / Rent costs across divisions? The relationship between Division in the Lookup Table and Division in the "Aggregated Dataset" is active yet it does not seem to read it.
Thanks!
Chris
