Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

average in column per month

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.

Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Additionnal information : draft

    template

  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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.