Forum Discussion

aquamad96's avatar
aquamad96
Frequent Visitor
1 year ago
Solved

Matrix showing wrong values

I am having a problem with matrix tables showing the correct values for my measures.

I have a table with Regions as rows, then I have my average $ per kg measures as values, including 3M and 12M averages.

However it seems these values will not populate the table correctly and just stick to the same average $ per kg value in column 2.

 

 

 

My measure for 3M Rolling is here:

 

3M Average Cumulative $ per kg =
VAR Three = CALCULATE([Average $ per kg],DATESINPERIOD('Air Freight Spend'[MonthYear], MAX('Air Freight Spend'[MonthYear]), -3, MONTH))

RETURN IF(MAX('Air Freight Spend'[MonthYear]) >= DATE(2022, 3, 1), Three, BLANK())

 

Is this something to do with my month year filter?

 

 

Need Help
  • rajendraongole1's avatar
    rajendraongole1
    1 year ago

    Hi aquamad96 -you can use SUMMARIZE and CALCULATETABLE, to gain more control over the rolling period, ensuring that you're averaging the last 3 months correctly.

    modified measure:

    3M Average Cumulative $ per kg =
    VAR LastVisibleDate = MAX('Air Freight Spend'[MonthYear])
    VAR ThreeMonthPeriod =
    CALCULATETABLE(
    SUMMARIZE(
    'Air Freight Spend',
    'Air Freight Spend'[MonthYear],
    "AvgPerKg", [Average $ per kg]
    ),
    DATESINPERIOD(
    'Air Freight Spend'[MonthYear],
    LastVisibleDate,
    -3,
    MONTH
    )
    )
    VAR ThreeMonthAvg =
    AVERAGEX(ThreeMonthPeriod, [AvgPerKg])

    RETURN
    IF(
    LastVisibleDate >= DATE(2022, 3, 1),
    ThreeMonthAvg,
    BLANK()
    )

     

    Hope it helps, check the above measure let know 

     

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    1 year ago

    Hi aquamad96 -you can use SUMMARIZE and CALCULATETABLE, to gain more control over the rolling period, ensuring that you're averaging the last 3 months correctly.

    modified measure:

    3M Average Cumulative $ per kg =
    VAR LastVisibleDate = MAX('Air Freight Spend'[MonthYear])
    VAR ThreeMonthPeriod =
    CALCULATETABLE(
    SUMMARIZE(
    'Air Freight Spend',
    'Air Freight Spend'[MonthYear],
    "AvgPerKg", [Average $ per kg]
    ),
    DATESINPERIOD(
    'Air Freight Spend'[MonthYear],
    LastVisibleDate,
    -3,
    MONTH
    )
    )
    VAR ThreeMonthAvg =
    AVERAGEX(ThreeMonthPeriod, [AvgPerKg])

    RETURN
    IF(
    LastVisibleDate >= DATE(2022, 3, 1),
    ThreeMonthAvg,
    BLANK()
    )

     

    Hope it helps, check the above measure let know 

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi aquamad96 ,

     

    Based on the information you provided, I have created the following data table.


    Your 3M Rolling measure works fine for me.

     

    Can you provide some sample data? We can better understand the problem and help you.

    Or show it as a screenshot or pbix. Please remove any sensitive data in advance. If uploading pbix files please do not log into your account.

    Please see the attached pbix for refrence.

    Best Regards,

    Dengliang Li

     

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