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 everyone,
I'm having trouble to correctly link two tables. Let's call them Revenue and Forecast. Below you can find a
Both Revenue and Forecast are linked via the date with a 1 to many relationship with a table Calendar.
As the Revenue table has multiple transactions for the same client and the same kind on the same day, it's not possible to merge the queries (because this way I get duplicates of the Forecast table, resulting in a too high Forecast). If I try to solve it with relationship between tables, I have a many-to-many relationships. Neither way I succeed in getting the right result. At the moment the only solution I see is making a pivot table of the Revenue table but as I already have many things running with this table, I would prefer to avoid this.
Revenue table:
Date | Client | Revenue | transaction kind |
01/01/2021 | A | 30 | |
01/01/2021 | A | 30 | Parcels |
01/01/2021 | A | 60 | |
01/01/2021 | B | 400 | Parcels |
01/01/2021 | B | 200 | Parcels |
01/01/2021 | C | 30 | |
02/01/2021 | A | 30 | |
02/01/2021 | A | 60 | parcels |
02/01/2021 | B | 400 | Parcels |
02/01/2021 | C | 200 | Parcels |
Forecast
Date | Client | Forecast | Transaction kind |
01/01/2021 | A | 100 | Parcels |
01/01/2021 | B | 500 | Parcels |
01/01/2021 | C | 2500 | Parcels |
02/01/2021 | A | 110 | Parcels |
02/01/2021 | B | 550 | Parcels |
02/01/2021 | C | 2000 | Parcels |
01/01/2021 | A | 10 | |
01/01/2021 | C | 100 | |
02/01/2021 | A | 50 | |
02/01/2021 | C | 1000 |
Solved! Go to Solution.
@Mdobbels , You need to have common Table/dimension tables Transaction kind, Date and client and join them with both tables and analyze
Create common table -https://www.youtube.com/watch?v=Bkf35Roman8
@Mdobbels , You need to have common Table/dimension tables Transaction kind, Date and client and join them with both tables and analyze
Create common table -https://www.youtube.com/watch?v=Bkf35Roman8
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |