Calculating averages for a selection with changing reference

Hi Anonymous ,

Sorry for my late reply.

I did not establish a relationship between the two tables to avoid creating a third table.

New measures of Task 3:

(PL)Average price_cm = 
IF (
    ISFILTERED ( 'dim_FL'[PL] ),
    DIVIDE (
        CALCULATE (
            SUM ( 'fact_price'[price_cm] ),
            FILTER ( 'fact_price', [FL] IN VALUES ( 'dim_FL'[FL] ) )
        ),
        CALCULATE (
            COUNT ( 'dim_FL'[tag] ),
            FILTER ( 'dim_FL', [tag] = MAX ( 'fact_price'[Tag] ) )
        )
    )
)

(PL)Average price_pm = 
IF (
    ISFILTERED ( 'dim_FL'[PL] ),
    DIVIDE (
        CALCULATE (
            SUM ( 'fact_price'[price_pm] ),
            FILTER ( 'fact_price', [FL] IN VALUES ( 'dim_FL'[FL] ) )
        ),
        CALCULATE (
            COUNT ( 'fact_price'[Tag] ),
            FILTER (
                'fact_price',
                [Tag]
                    IN VALUES ( 'dim_FL'[tag] )
                        && [price_pm] <> BLANK ()
                        && [FL] IN VALUES ( 'dim_FL'[FL] )
            )
        )
    )
)

I added IF (ISFILTERED ( 'dim_FL'[SFL] ), to other measures. 

For more details, you can check from here.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.