Average for table depending on number of months displayed

[Szwadron]

Hi everyone, 

I am struggling to find an appropriate measure code that I could use in a card visualisation that would show me average of displayed values per month.

Year	January	February	March	April	May	June	July	August	September
2021	100	110	120	130	140	150	160	170	180
2022	250	260	270	280	290	300	310	320	330
2023	360	370	380	400	420	430	480

So let's say I have a table like above that is calculated based on Costs table from my data model and I would like to be able to select the entire row by clicling on the right year and a card visualisation would show me average of selected period. But here is the problem I have that I cannot just divide by 12 months since for current year I only have data till current month so:
1) for all previous years it should be divided by 12 months
2) for current year by numbers of months that appear in my data model

or in another words I would like to divide values that are shown in a table by numer of columns in a table including when value is 0 and excluding blanks.

Is that achievable with a measure or I need to use calculated column?

I would really apprecaite your hints.

Thank you in advance

[johnt75 · Accepted solution]

If the measure you are using in the visual is [Costs] then you could use

Avg Costs =
AVERAGEX ( SUMMARIZE ( 'Date', 'Date'[Year month] ), [Costs] )

If no year is selected it will show the average over all time. If a year is selected then it will show the average for that year.

[MAwwad · Accepted solution]

You can create a measure that calculates the number of months displayed in the table and use it to calculate the average. Here's an example measure:

Avg Cost = VAR NumOfMonths = COUNTAX( FILTER( ALLSELECTED( Dates ), NOT(ISBLANK( [YourCostMeasure] )) ), [Month] ) RETURN DIVIDE( [YourCostMeasure], NumOfMonths )

Replace [YourCostMeasure] with your cost measure, and [Month] with the column that contains the month information in your date table.

The measure first calculates the number of months displayed in the table by counting the non-blank columns for the selected year. It then divides the cost measure by the number of months displayed.

In the visual, you can select the year to display and the measure should calculate the average based on the number of non-blank months in the selected year.

[johnt75 · Accepted solution]

If the measure you are using in the visual is [Costs] then you could use

Avg Costs =
AVERAGEX ( SUMMARIZE ( 'Date', 'Date'[Year month] ), [Costs] )

If no year is selected it will show the average over all time. If a year is selected then it will show the average for that year.