How to get DAX measures right for SUM and AVERAGE

[Sebastiaan99]

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 are:
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: https://1drv.ms/u/s!AmD4aHeJ1Mpgillb3_YcIvzQaYUv?e=0JVedB

Kind regards,
Sebastiaan Vermeulen

[PaulOlding]

Hi Sebastiaan,

Here's what I have with some revised measures.  Hopefully this is what you want:

For the weighted average I created a base measure:

Weighted Price =

VAR _Num = SUMX('Price plans', 'Price plans'[Price] * RELATED(Aircrafts[Capacity]))

VAR _Denom = SUM(Aircrafts[Capacity])

VAR _Result = DIVIDE(_Num, _Denom)

RETURN

_Result

using AVERAGEX wouldn't work here because the denominator would be the number of price plans rather the the sum of capacity.

Then I have 2 measures built on top of that for Low and High Season

Average Weight Price Low Season =

CALCULATE(

[Weighted Price],

'Price plans'[Season] = "Low"

)

the high season one is much the same, just replace "Low" with "High".

Now the bar chart.  Here the issue is with filter direction.  The relationship between Aircrafts and Price Plans means that Aircrafts filters Price Plans, and not the other way around.  So in your chart for the Price = 6 bar, for example, the Aircrafts table is not being filtered for Aircrafts where the price is 6.  The filter cannot go that way.

However, values in the table on the one-side of a relationship are available when iterating the table on the many-side.  So, we can rewrite the Capacity measure so it iterates Price Plans instead:

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

[Sebastiaan99]

Wow, this is sort of amazing! Wonderful.

A couple of questions though.

The issue with Capacity having to do with the filter direction is as good as clear by the explanation afterwards.. I am only puzzling how I can recgonize such a situation in advance. But wonderful, anyway.

Does this als apply to my DAX measure for No. of Aircrafts? I added this measure in the tooltip, so when the user hovers over the bar chart, they receive information about the number of aircrafts from each airline that have this particular price. At the moment this measure isn't showing the right figures either. 

Then the weighted average. My -by hand calculated- weighted average price for low season was a bit different (9.344 instead of 9.46). Could it be that your denominator also includes the capacity of Aircraft 5 and 6, as they do have a capacity, but no price scheme available for these aircrafts? In my opinion the weighted average should only be calculated for the airlines, aircrafts and price schemes being selected. Therefore I was also surprised to see both an average price for Low season and for High season displayed in your screen image, although the slicer was set to display only Low season.
I intend to make use of the filter pane, allowing the end user to filter on particular aircrafts, airlines, countries, whatever. As well as the displayed slicers for High and Low season and Standard and/or Premium price plan.  And the bar chart as well as the calculated weighted avareage should only take into account those price schemes and aircrafts and airlines that pass al these slicers and filters. What does this mean for the DAX Measure? Need only the denominator  to be adjusted for the proper filter context? Or also the Numerator?

Thanks a lot whith you answer so far!

[PaulOlding]

Does this als apply to my DAX measure for No. of Aircrafts? 

Yes.  You could count AircraftID from your fact table (Price Plans) to resolve this:

No. of Aircrafts = COUNT('Price plans'[Aircraft ID])

My -by hand calculated- weighted average price for low season was a bit different (9.344 instead of 9.46)

I think you're right with the diagnosis.  A change in Weighted Price measure gets the correct result:

Weighted Price =

VAR _Num = SUMX('Price plans', 'Price plans'[Price] * RELATED(Aircrafts[Capacity]))

VAR _Denom = SUMX('Price plans', RELATED(Aircrafts[Capacity]))

VAR _Result = DIVIDE(_Num, _Denom)

RETURN

_Result

I was also surprised to see both an average price for Low season and for High season displayed in your screen image, although the slicer was set to display only Low season.

If you want the measure to be responsive to the slicer selection you can just use the base measure (Weighted Price).  The CALCULATE statement in the Average Weight Price Low Season measure is overriding what is selected in the Season slicer, so always shows the Low season figure.

I intend to make use of the filter pane, allowing the end user...

These measures would all be responsive to filters / slices from other fields.

I am only puzzling how I can recgonize such a situation in advance

This I think stems from your model.  Properly formed star schema models are generally easier to use once you get to the DAX writing & visualisation stage.  Your model as it is requires extra thought / investigating.  I'll expand on that in another reply, this one's already quite long...

[Sebastiaan99]

Thanks Paul! This works very well!

I even adjusted the Weighted price measure to be able to show seperatly a Low season weighted price and a High season weighted price in case both Low and High season are selected in the slicer: 

Weighted price LOW Season =
VAR _Num = SUMX(
FILTER('Price plans',
'Price plans'[Season] = "Low"
),
'Price plans'[Price] * RELATED(Aircrafts[Capacity]))
VAR _Denom = SUMX(
FILTER('Price plans',
'Price plans'[Season] = "Low"),
RELATED(Aircrafts[Capacity]))
VAR _Result = DIVIDE(_Num, _Denom)
RETURN
_Result

This really helped me out!

Additionally, I like to count the number of Airlines involved in the entire bar chart. In much teh same way as you created the measure for the sum of capacity involved, I can create this measure for the number of airlines:

No. of Airlines = COUNTX('Price plans', RELATED(Airlines[Airline ID]))

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

Perhaps the measure should first create a table with all related Airline ID's and after that execute a distinct count over that table?

[PaulOlding · Accepted solution]

The way I got an airline count working with your model was 

No. of Airlines = CALCULATE(COUNTROWS(Airlines), 'Price plans')

[PaulOlding]

So, star schemas:

First, we should consider the types of fields we have in the model:

facts = a number you're going to aggregate in some way

dimensional values = something you're going to slice / dice the facts by

keys = no inherent meaning, just used to join tables together.

In a star schema the fact fields should go on the fact table & dimensional values should go on dimension tables.  In your model (which is star-like with it's one-to-many relationships) you're aggregating capacity (so it's a fact field) but it's on the Aircraft table (a dimension).  This is why we're having to explicitly iterate the Price Plans table in the measures and use RELATED(Capacity).  If Capacity was on the fact table you'd just reference 'Fact Table'[Capacity] and there's no need to think about it.

Now, on to Price.  This is interesting because it's being aggregated in the Weighted Price measure (so it's a fact field) and then you're slicing by it in the bar chart (so it's a dimensional value).  The purists approach to this would be to have Price on both the fact table and in a dimension table.  I wouldn't be surprised if Capacity fell into this category too as you may want to slice by that.  I wonder once you get to your real data if you really want to slice by Price?  Could you have, say 11.32 and 11.36 and 11.42 etc?  A common approach to this is to have Price Bands on a dimension (eg 0 to 5.99, 6 to 11.99, 12 to 19.99 etc) rather than the actual price.

Anyway, a revised model might look like this:

Now your Fact table only contains keys and facts.  This means it can be hidden as we don't use the keys in visualisations and we'll use measures to aggregate the facts.

When it comes to using Price on the bar chart, you'd select it from the dimension table.

The measures for this model would be:

Capacity = SUM('Fact Price Plans'[Capacity])

No. of Aircraft = COUNT('Fact Price Plans'[Aircraft ID])

Weighted Price =

VAR _Num = SUMX('Fact Price Plans', 'Fact Price Plans'[Price] * 'Fact Price Plans'[Capacity])

VAR _Denom = SUM('Fact Price Plans'[Capacity])

VAR _Result = DIVIDE(_Num, _Denom)

RETURN

_Result

[Sebastiaan99]

You have a good point regarding the price bands. I checked my data and indeed I have many prices rather close to each other. My first thought would be to use Power Query to create an extra column for table Price plans in which I define price bands (e.g. "10-12" when price=>10 and price<=12).

But perhaps you have a better aproach for this in DAX, including an option to make the price bands dynamic depending on the distribution of the prices in the sliced & filtered subset of the Price plan table?

Regarding the explaination on the data model, I find that sometimes a bit hard to grasp. It's interesting what you point out and I recognize it from many other discussions on data models regarding Power BI. But I am from the time in history we learned about relational datamodelling ('90's receiving lessons in SQL at school). And I always tend to figure out what a field is describing. Capacity to me is cleary an aspect of an Aircraft and not of a Price scheme. So is price clearly an aspect of a Price scheme. Therefore I always feel a bit awkward with the concepts of Facts and Dimensions. Especially because the wish to use a certain field for slicing (and therefore turning it into a dimensional value) might change overtime. That would mean that introducing a new data view and visual where I use a field to slice for the first time, would mean I have to change my data model.

In fact, according to the 'purist' perception we are storing the field Capacity and the field Price in two places. I remember one of the golden rules to be that data should always be stored at one single place. 

I have to investigate this matter a bit more thoroughly I guess! 

Thanks for your helpful views and suggestions. And keep me posted if you have any ideas about using price bands.