Derive Avg based on Dimension from other table

[mbudiman]

Hello,

I need help to derive average shipped qty for last 3 Qtr for each REGION.

The sample pbix can be downloaded here Inventory2.pbix

I use custom Calendar that contains these columns :

- Fiscal Year, example : FY25, FY24 

- Fiscal Qtr, example : FY25Q1, FY25Q2, FY25Q3

- Qtr_Seq_No, where 0 means current Qtr, -1 means last 1 Qtr, -2 means last 2 Qtr

So to derive last 3 Qtr, we use Qtr_Seq_No -3, -2, -1.

Inventory table contains these columns :

- Fiscal Qtr

- Hub

- Product

- Shipped Qty

Inventory Region table contains these columns:

- Region

- Hub

Table Relationship

Calendar table is linked to Inventory table by Fiscal Qtr column

Inventory Region table in linked to Inventory table by Inventory Hub.

The following is the required report format and expected result.

What is the DAX statement to achieve this ? Thank you in advance for your help.

[danextian · Accepted solution]

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.

[mbudiman]

hi @danextian,

Thank you for the solution with clear explanation. Appreciate that.

best regards,

mbudiman