Derive Avg based on Dimension from other table

Hi mbudiman 

For the calcuations to be dynamic, the sequence must not be hardcoded but rather must be determined from the currently selected sequence or if the current  one, zero.

Average L3Q = 
VAR CurrentSequence =
    -- The current quarter sequence number in the context.
    MIN ( 'Inventory calendar'[QTR_SEQ_NO] )
VAR OneQtrAgo =
    -- Calculate the sequence number for one quarter ago.
    CurrentSequence - 1
VAR ThreeQtrAgo =
    -- Calculate the sequence number for three quarters ago.
    CurrentSequence - 3
VAR _Numerator =
    -- Calculate the total "Shipped Qty" for the last three quarters.
    CALCULATE (
        SUM ( Inventory[Shipped Qty] ), -- Total shipped quantity
        FILTER (
            ALL ( 'Inventory calendar' ), -- Ignore existing filters on the calendar table
            'Inventory calendar'[QTR_SEQ_NO] >= ThreeQtrAgo
                && 'Inventory calendar'[QTR_SEQ_NO] <= OneQtrAgo -- Restrict to the desired range of quarters
        )
    )
VAR _Denominator =
    -- Calculate the number of quarters in the range dynamically (if constant, use 3 instead).
    VAR ConstantDenominator = 3
    VAR DynamicDenominator =
        CALCULATE (
            COUNTROWS ( 'Inventory calendar' ), -- Count the number of rows (quarters) in the calendar
            FILTER (
                ALL ( 'Inventory calendar' ), -- Ignore existing filters on the calendar table
                'Inventory calendar'[QTR_SEQ_NO] >= ThreeQtrAgo
                    && 'Inventory calendar'[QTR_SEQ_NO] <= OneQtrAgo -- Restrict to the desired range of quarters
            )
        )
    RETURN
        DynamicDenominator
RETURN
    -- Divide the total shipped quantity by the number of quarters to get the average.
    DIVIDE ( _Numerator, _Denominator )

The last 3 quarters average for each region can be calculated as below:

Average L3Q  - America = 
CALCULATE ( [Average L3Q], 'Inventory Region'[Region] = "America" )

Please see the attached pbix.