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 ave...
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 

     

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