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.
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.
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.
Hi @Sebastiaan, did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
Thanks for your response! The calculations run indeed correctly. My only surprise is that the measure doesn’t respond to the slice selection for Low or High season. Either the average for Low and High season are still being calculated, regardless the selection in the slicer.
I see now how I should start fro the Price plan table and work my way back to the Aircraft data.
But how would that work if I would like to count the number of Airlines involved in the bar chart?
Using your approach with RELATED TABLE could result in:
No. of Airlines = COUNTX(‘Price plans’, RELATED(Airlines[Airline ID]))
But that doesn’t work, because in order to count the number of airlines correctly I need to count the distinct Airline ID’s. How can I change this measure so that it counts the number of distinct Airline ID’s from the related table?
Attempts like these give errors:
No. of Airlines = DISTINCTCOUNT(‘Price plans’, RELATED(Airlines[Airline ID]))
And this attempt:
No. of Airlines = CALCULATE(COUNTROWS(Airlines), ‘Price plans’)
doesn’t work either, as this measure also counts the airlines for which no price plans are present (Air France in my datamodel).
So I need to start from the Price plan table, take into account the filters from all slicers and filter panels and then count the number of distinct airline ID’s related to these price plans. Perhaps the measure should first create a table with all related Airline ID’s and after that execute a distinct count over that table?
But how to write that in DAX?
Thanks, but there is something strange in this measure:
Count Airlines Id =
COUNTROWS (
DISTINCT (
FILTER (
VALUES ( Aircrafts[Aircraft ID] ),
Aircrafts[Aircraft ID] IN ALLSELECTED ( ‘Price plans’[Aircraft ID] )
)
)
)
The measure is about to count the number of Airlines, but the only thing in the DAX I see is Aircraft ID instead of Airline ID. The measures now returns 3 (as there exist 3 airlines in the datamodel) and does not change regardless what price schemes are selected. The Airlines are in the table Airlines and the Aircafts in a separate table Aircrafts.
Can you adjust the measure for that aspect?
You are right , measure was not correct. Please try below measure. Not able to test properly as your sample data returns same number for all combinations. If not working, then modify sample data or share source spreadsheet.
Count Airlines Id =
var aircraftids = FILTER (
Aircrafts,
Aircrafts[Aircraft ID] IN VALUES( 'Price plans'[Aircraft ID] )
)
return
countrows(SUMMARIZE(aircraftids,Airlines[Airline ID]))
Hi @Sebastiaan did the response provided by @ankit help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!
