Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi Guys,
I hope you can help me with the problem I'm facing.
I have two tables, let's say Contract and Revenue.
Revenue
| Customernr. | Subcategory | Subcategory (Groups) | Revenue in € |
| 1 | Blue Paint | Paint | €40 |
| 1 | Red Paint | Paint | €50 |
| 2 | Planks | Wood | €170 |
Contract
| Customernr. | Category | Amount in € |
| 1 | Paint | €100 |
| 1 | Metal | €250 |
| 1 | Wood | €200 |
| 2 | Paint | €500 |
| 2 | Metal | €250 |
| 2 | Wood | €200 |
In my Report view I want a matrix table with the customer number and category with the amount in € and the SUM of revenue in € behind it. That way I can check if the customer buys under the contracted amount.
The problem I am facing right now is that I cant merge these two tables because I use Groups. Groups aren't visible in the query editor. So I have to solve this problem outside the query editor.
What is the best way to sum the revenue in € based on the customer number and subcategory (groups)?
This is what I want:
| Customernr. | Category | Amount in € | Revenue in € |
| 1 | Paint | €100 | €90
| 1 | Metal | €250 | €0
| 1 | Wood | €200 | €0
| 2 | Paint | €500 | €0
| 2 | Metal | €250 | €0
| 2 | Wood | €200 | €170
Solved! Go to Solution.
I figured it out with the replies on this post.
I could not create a composite key in the query editor, because I have groups.
So I created a new column in both tables that uses the two columns and merges them as one. This is my key.
Now I could create a relationship between the two tables.
I figured it out with the replies on this post.
I could not create a composite key in the query editor, because I have groups.
So I created a new column in both tables that uses the two columns and merges them as one. This is my key.
Now I could create a relationship between the two tables.
Hi Tim, can you not create a compcite key in the query editor uing the two fields and then join the tables on the composite key?
something like this
Revenue | ||||
Customernr | Subcategory | Subcategory (Groups) | Revenue in E | composite key |
1 | Blue Paint | Paint | € 40 | 1 Paint |
1 | Red Paint | Paint | € 50 | 1 Paint |
2 | Planks | Wood | € 170 | 2 Wood |
Contract | ||||
Customernr | Category | Amount in eur | composite key | |
1 | Paint | € 100 | 1 Paint | |
1 | Metal | € 250 | 1 Metal | |
1 | Wood | € 200 | 1 Wood | |
2 | Paint | € 500 | 2 Paint | |
2 | Metal | € 250 | 2 Metal | |
2 | Wood | € 200 | 2 Wood |
@Anonymous Please create a column by merging Customernr and Subcategory (Groups) in Revenue table.
Similarly create one column by merging Customernr and category in contract table and create a relationship between two tables
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |