Forum Discussion
Measure from two tables
Hi guys,
I'm new to Power BI and I'd love your help about the following calculation.
There are two tables: Fee and Passenger.
Table 1: Fee
| Airline | Date | Sales Channel | Fee Count |
| AA | 03/05/2018 | A | 5 |
| AA | 04/05/2018 | A | 6 |
| AA | 05/05/2018 | B | 23 |
| AA | 06/05/2018 | A | 2 |
| AA | 06/05/2018 | B | 12 |
| AA | 06/05/2018 | C | 5 |
| BB | 05/05/2018 | A | 8 |
| BB | 05/05/2018 | C | 14 |
| BB | 07/05/2018 | A | 9 |
Table 2: Passenger
| Airline | Date | Passenger Count |
| AA | 03/05/2018 | 102 |
| AA | 04/05/2018 | 105 |
| AA | 05/05/2018 | 200 |
| AA | 06/05/2018 | 210 |
| BB | 05/05/2018 | 168 |
| BB | 05/05/2018 | 195 |
| BB | 07/05/2018 | 144 |
Fee Table has a column called Sales Channel and Pax Table does not.
How could I connect both tables to get the Buy rate SUM(Fee Count) / SUM(Passenger Count)?
I really appreciate your help.
3 Replies
Hi carlosz22,
If you're looking to split this by Airline and/or date, it'd make sense to have an "Airline" and/or "Date" table with the values that you need and then join the table to your existing Fee and Passenger tables.
Hi carlosz22,
We cannot create the relationship between the two tables directly because of the many to many raltionship which is not supported in Power BI.
So we could create the third table that has distinct value of Airline from both tables.
Table = DISTINCT(UNION(DISTINCT(Passenger[Airline]), DISTINCT(Fee[Airline])))
Then you could create the measure to get the Buy rate .
Measure 2 = DIVIDE(SUM('Fee'[Fee Count]),SUM('Passenger'[Passenger Count]))Hope this can help you!
Best Regards,
Cherry
Hi carlosz22,
Have you solved your problem?
If you have solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
If you still need help, please let me know.
Best Regards,
Cherry
