DAX measures for Summing up and Weighted average

Hi,

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.

Kind regards,
Sebastiaan Vermeulen
Airline price plan.pbix (39.7 KB)

Bumping this post for more visibility.

Hi @Sebastiaan

Please check the attached file and see if it is helpful.
Airline price plan_Solution.pbix (42.2 KB)

As it need values from One side of relationship from Many side, make use of Related function.

Airline Capacity =
CALCULATE ( SUMX ( 'Price plans', RELATED ( Aircrafts[Capacity] ) ) ) 

Average Low measure =
CALCULATE (
    DIVIDE (
        SUMX ( 'Price plans', 'Price plans'[Price] * RELATED ( Aircrafts[Capacity] ) ),
        [Airline Capacity]
    ),
    'Price plans'[Season] = "Low"
)

Thanks
Ankit J

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!

Hi Ankit,

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?

Perhaps you can give a suggestion or that?

Regards,
Sebastiaan

Hi @Sebastiaan

For First, I added Calculate function as based on screnshots I assume you need them to not affeected by Slicer. For Average to affect by Slicer use

Average Measure = DIVIDE (
        SUMX ( 'Price plans', 'Price plans'[Price] * RELATED ( Aircrafts[Capacity] ) ),
        [Airline Capacity]
    )

For second, I assume you need to show it in a Card visual. For that, can try below expression. it is primarily doing what you suggested in approach.

Count Airlines Id =
COUNTROWS (
    DISTINCT (
        FILTER (
            VALUES ( Aircrafts[Aircraft ID] ),
            Aircrafts[Aircraft ID] IN ALLSELECTED ( 'Price plans'[Aircraft ID] )
        )
    )
)

Thanks
Ankit J

Hi Ankit,

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?

Regards,
Sebastiaan

Hi Ankit,

Is it possible for you to have another look at the last measure you suggested and make it work?

Regards,
Sebastiaan

Hi @Sebastiaan

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]))

Thanks
Ankit J

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! This seems to work much better.