iterator

[Cyriackpazhe]

I dont understand the values at month level. Shouldn't it display the same values as the years since ALL is used in the measure. Could someone explain.

[v-kongfanf-msft]

Hi @Cyriackpazhe ,

Did @bhanu_gautam  reply solve your problem? If so, please mark it as the correct solution, and point out if the problem persists.

Best regards,

Adamk Kong

[bhanu_gautam · Accepted solution]

@Cyriackpazhe 

The confusion arises from the use of the ALL function in your DAX measure. The ALL function removes all filters from the specified column or table, which can lead to unexpected results if not used correctly.

Your measure is defined as:

AVERAGEX(ALL('Calendar'[Date].[Year], 'Calendar'[Date].[Month]), [Total])

This measure calculates the average of [Total] over all years and months, ignoring any filters applied to the Year and Month columns. This means that the measure will compute the average of [Total] across the entire dataset, not just for the specific year or month in the current context.

If you want the measure to display the same values at the month level as it does at the year level, you need to ensure that the measure respects the current context of the year and month. You might want to use a different approach that maintains the context of the year and month. For example, you could use the CALCULATE function to modify the filter context:

AVERAGEX(
CALCULATETABLE(
VALUES('Calendar'[Date]),
ALL('Calendar'[Date].[Year])
),
[Total]
)
This measure calculates the average of [Total] for each month within the context of the current year, rather than removing the filters entirely. This way, the measure will respect the current year context while still calculating the average for each month.

[Cyriackpazhe]

My confusion is since i'Ve used ALL , shouldn't the value at the month level be same as year level since all filters will be ignored. Why Shouldn't January 2016 display the same value as 2016. But here , January 2016 is same as January 2017. Shouldn't the value on the entire table be the same.

[bhanu_gautam]

The reason why January 2016 displays the same value as January 2017 is because the ALL function removes the filters on both the Year and Month columns, causing the measure to calculate the average [Total] across all years and months. Therefore, the value for each month is the same across different years, but not necessarily the same as the yearly total.