Computing average over hierarchies

DAX DAX Calculations
1

Hello,
I would like to ask for some help computing averages.
Here is the Power bi file attached:

DemoModel.pbix (198.6 KB)

I need to compute Average price as % of Inside cabin total.
i.e. calculate Average price of Inside cabin (all cabins under inside category),
and use this value as Denominator for calculating percentage pr cabin code.

Here is how the final result should look like:

How the result should look like
How the result should look like1081Ă—589 18.8 KB

I have started from this Measure and the underlying result:

The result w 1 value filtered
The result w 1 value filtered1275Ă—657 44.8 KB

And I did some tests with following measures.
However the result was wrong

several tests
several tests1366Ă—757 57.6 KB

I do understand that my context is not properly build up for this Parent-child hierarchy and that’s why I need some help.

2

Hello @tyankata,

Thank You for posting your query onto the Forum.

To achieve the results based on the condition that you’ve specified i.e., divide individual row of “Avg_Price_Cabin_DBL” with the total figure of “Inside”. Below is the one single measure provided for the reference (I’ve used the variable technique but in your file you can replace it with the measure branching technique, if you want) -

Avg Price as % of Inside cabin K3 - Harsh = 
VAR _Projected_Revenue_Cabin_Double = 
SUMX( Fact_CabinPrices,
    Fact_CabinPrices[Cabin Price Double] * Fact_CabinPrices[Cabins Avail.] )

VAR _Cabin_Avail_Real =
SUM( Fact_CabinPrices[Cabins Avail.] )

VAR _Avg_Price_Cabin_DBL = 
DIVIDE(
    _Projected_Revenue_Cabin_Double ,
    _Cabin_Avail_Real ,
    0 )


VAR _Avg_Price_Of_Inside_Cabin_DBL = 
CALCULATE(
    VAR _Projected_Revenue_Cabin_Double = 
    SUMX( Fact_CabinPrices,
        Fact_CabinPrices[Cabin Price Double] * Fact_CabinPrices[Cabins Avail.] )

    VAR _Cabin_Avail_Real =
    SUM( Fact_CabinPrices[Cabins Avail.] )

    VAR _Avg_Price_Cabin_DBL = 
    DIVIDE(
        _Projected_Revenue_Cabin_Double ,
        _Cabin_Avail_Real ,
        0 )

    RETURN
    _Avg_Price_Cabin_DBL , 
        ALL( dim_cabin_properties[Cabin Code] ) , 
            dim_cabin_properties[Standardized Cabin] = "Inside" )

RETURN
DIVIDE( 
    _Avg_Price_Cabin_DBL ,
    _Avg_Price_Of_Inside_Cabin_DBL , 
    0 )

Once you write this measure, you’ll be able to see the desired results. Below is the screenshot of the final results provided for the reference -

Final Results
Final Results1248Ă—948 58.2 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Important Note: The % results that you’re showing in the form of Excel screenshot is actually incorrect. For example, when a figure of 27,058 (Inside Plus - J2) is divided with 23,367 it should be 115.79% and not 118.60%. This is happening for most of the rows that you’re showing. Please check once

Thanks and Warm Regards,
Harsh

DemoModel - Harsh.pbix (195.6 KB)

2 Likes
3

Thank you, Harsh!
It is now tested in the real model and it works perfectly.
After thoroughly studying your measure, I found out where were my mistakes when creating the right context.

Thanks again!

P.S. you are right about the Excel screenshot. The mistake there is found out. :slight_smile:

Best regards,
Tyankata

4

Hello @tyankata,

You’re Welcome!! :slightly_smiling_face:

I’m glad that I was able to assist you.

Thanks and Warm Regards,
Harsh