average in column per month

[Anonymous]

Hi Dear,

I have a question avout average.

I have 2 tables A & B.

In both tables, I have Date Column and Cost Column

Table A can have many row per month but table B have one row per month

In table A, I want to calcutate average of cost of table B like cost B divide by number of item per month (for the same month ) in table B and return in each row the average cost of month.

e.g : if average cost (cost B/Number of item A) is 17€ for April 2022, in my new colum in table A, for each row the value must be 17€. *

So if 170€ (table B) for 10 rows (table A), in table A, 17€ for each rows

So it's like a distribution of cost B to all rows of table A for same month

Thanks you for fyour help.

Wilfried.

[v-yanjiang-msft · Accepted solution]

Hi @Anonymous ,

According to your description, here's my solution.

Create a calculated column.

Column =
VAR _B =
    MAXX (
        FILTER (
            'Table B',
            MONTH ( 'Table B'[Date] ) = MONTH ( EARLIER ( 'Table A'[Date] ) )
        ),
        'Table B'[Cost]
    )
VAR _Count =
    COUNTROWS (
        FILTER (
            'Table A',
            MONTH ( 'Table A'[Date] ) = MONTH ( EARLIER ( 'Table A'[Date] ) )
        )
    )
RETURN
    DIVIDE ( _B, _Count )

Get the expected result.

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

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

[Anonymous]

Additionnal information : draft

template