Average per month

[abukapsoun]

Hi, 

I have the following table

Date                       Amount               Sign

21/03/2019              -444                    -1

22/03/2019              -123                    -1

23/03/2019                111                    1

.

.

03/08/2019               -324                     -1

.

.

Where the -1 tells this is an outflow, and 1 this is an inflow. 

I am looking to calculate the total average inflow , and the total average outflow of the whole period. For example in the table i have shown I have 6 months, from March to August. What is the average of inflow and outflow. 

Thanks,

[jdbuchanan71]

Hello @abukapsoun ,

Give these two measures a try:

Avg Inflow = 
CALCULATE(
    AVERAGE( table[Amount] ),
    KEEPFILTERS ( table[Sign] = 1 )
)

Avg Outflow = 
CALCULATE(
    AVERAGE( table[Amount] ),
    KEEPFILTERS ( table[Sign] = -1 )
)

[abukapsoun]

Hi,

Thank you very much.

But this is giving the average per count no? It is not taking into consideration the date (here i am looking for month). the total outflow needs to be devided by the number of months

Regards,

[jdbuchanan71]

So if you have 6 months selected and you pull month into a table along with the average you want the answer to be the same for every month?  Total Outflow for the 6 months / 6. 
If so that looks like this:  This will require you to have a date table with a column for Month and Year.

Total Avg Outflow = 
VAR SelectedMonths = 
    CALCULATE (  
        COUNTROWS( VALUES ( Dates[Month Year] ) ), 
        ALLSELECTED( Dates ) 
    )
VAR TotalOutflowSelectedMonths = 
    CALCULATE (
        SUM( flow[Amount] ),
        flow[Sign] = -1,
        ALLSELECTED( Dates )
    )
VAR Result = DIVIDE ( TotalOutflowSelectedMonths, SelectedMonths )
RETURN Result

The image below shows what the two variables in the measure are calculating highlighted in Red and Green.  You don't need separate measures to do the final calc, I just like to build up measures that way, testing each step.  The final column is the result of the measure above.