Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
75 | |
63 | |
51 | |
48 |
User | Count |
---|---|
204 | |
86 | |
62 | |
59 | |
56 |