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.
The problem below occurs:
We have 2 database files. The first contains the number of hours per file number at activity level. In the second file you have the revenue amount per file number at activity level.
We want an overview of the number of hours spent and revenue made per file number per activity. But the activities do not have the same names and not every activity can be used for both the hours file and the turnover file.
Simplified example | ||||||||
Database for the hours | Database for the revenue | |||||||
Activity | File number | Worked ours | Activity | File number | Revenue | |||
AAC | 1234 | 0,250 | XYB | 1234 | € 150,00 | |||
AAD | 1234 | 0,125 | XYD | 1235 | € 200,00 | |||
AAA | 1235 | 0,500 | XYZ | 1236 | € 25,00 | |||
AAA | 1236 | 1,000 | XYZ | 1236 | € 75,00 | |||
AAB | 1236 | 0,250 | XYA | 1237 | € 125,00 | |||
AAC | 1236 | 0,125 | XYD | 1237 | € 175,00 | |||
AAD | 1236 | 0,500 | XYC | 1237 | € 50,00 | |||
AAC | 1237 | 1,000 | XYB | 1238 | € 25,00 | |||
AAD | 1237 | 0,250 | XYA | 1238 | € 100,00 | |||
So some activities are linked and others not. Like in the example below | ||||||||
Linking table | ||||||||
Activity hours | Activity revenue | |||||||
AAA | XYZ | |||||||
AAB | XYA | |||||||
AAC | ||||||||
XYD | ||||||||
AAD | XYC | |||||||
XYB | ||||||||
Now we want to see when the file number are the same if there are hours against the made revenue. | ||||||||
File number | Activity revenue | Activity hours | amount | ours | ||||
1234 | XYZ | AAA | € 50,00 | 0,500 | ||||
XYA | AAB | € 100,00 | 1,000 | |||||
XYB | € 150,00 | 0,000 | ||||||
AAC | € - | 0,250 | ||||||
AAD | € - | 0,125 | ||||||
1235 | XYD | € 200,00 | 0,000 | |||||
1236 | XYZ | AAA | € 100,00 | 1,000 | ||||
AAB | € - | 0,250 | ||||||
AAC | € - | 0,125 | ||||||
AAD | € - | 0,500 | ||||||
1237 | XYA | € 125,00 | 0,000 | |||||
XYD | € 175,00 | 0,000 | ||||||
XYC | AAD | € 50,00 | 0,250 | |||||
AAC | € - | 1,000 | ||||||
1238 | XYB | € 25,00 | 0,000 | |||||
XYA | € 100,00 | 0,000 |
Hi, @Svenvdh
According to your description and sample data, I can’t clearly understand what you want to get. Would you like to explain the columns in your expected output in detail(using some examples to explain why they are here and why their value is like this)?
Thanks very much!
How to Get Your Question Answered Quickly
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
110 | |
96 | |
77 | |
63 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |