Forum Discussion

abukapsoun's avatar
abukapsoun
Post Patron
7 years ago

Average per month

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,

Need Help

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Super User
    7 years ago

    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's avatar
      abukapsoun
      Post Patron
      7 years ago

      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's avatar
        jdbuchanan71
        Super User
        7 years ago

        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.