I am facing some difficulty with a few DAX formulas
The datamodel is about airlines, where each airline has one or more aircrafts. And each aircraft has a Standard and a Premium price scheme for both low and high season.
This is what the datamodel looks like:
And the content of the tables:
I would like to make a stacked bar chart showing how many seats are being offered at a certain price. And I need to calculate the weighted average price (price weighted by the capacity of the particular aircraft) for the selected low season and high season price plans. Therefore I like to make a dashboard that should look like this:
This requires DAX measures for: sum of capacity, number of aircrafts and weighted average price.
I cannot figure out how to get these measures right. The sum of capacity should be calculated per price value for each airline and I assumed that row context would make my DAX measure reflect this calculation per price value per airline. But it doesn’t. And also the calculation of the weighed average price doesn’t make much sense.
This is what my dashboard shows currently:
And the DAX measures I created:
Capacity = SUMX(Aircrafts, Aircrafts[Capacity])
No. of Aircrafts = COUNT(Aircrafts[Aircraft ID])
Average low season price = AVERAGEX(‘Price plans’, ‘Price plans’[Price] * [Capacity])
Not only is the capacity being calculated for the entire table regardless of the price level and airline, but also the capacity of aircrafts without a price scheme is being included.
Can anyone explain what my misunderstanding is here and how the DAX formulas should look like? I also attached the pbix-file I created.
Airline price plan.pbix (39.7 KB)