Calculate Average of Columns Values and Calculate Average Quarterly

[demder]

Hello to all, I have 2 questions. As you can see below, I have a bar chart. I want to calculate the average of the column and show it in the little box.  On the chart, I added the Average Line automatically, showing 165.42. It is the Average of the columns in the chart. I want to calculate the same thing with a formula. I used this formula for a little box. 

" Total_Cost average per ReservationID =

AVERAGEX(

    KEEPFILTERS(VALUES('Booking'[ReservationID])),

    CALCULATE(SUM('Booking'[Total_Cost]))

) " 

But this is wrong. It calculates the general average. There is a difference between the average line and the average box.

Secondly, I also want to calculate average values quarterly. Such as for 2019 Q1: (155.2 + 116.08 + 102.04)/3 = 124.44 also Q2, Q3 and Q4. After that, I will add this calculation to the graph.

I'm waiting for your help. Thank you!! 

@SpartaBI @amitchandak  @tamerj1 

[v-yanjiang-msft]

Hi @demder ,

According to your description, I create a sample.

Here's my solution, create a measure.

Total_Cost average per ReservationID =
DIVIDE (
    SUMX ( VALUES ( 'Booking' ), 'Booking'[Total_Cost] ),
    DISTINCTCOUNT ( Booking[Month] )
)

For the quarterly average value, create another measure.

Ave Quarterly =
AVERAGEX (
    FILTER (
        ALLSELECTED ( 'Booking' ),
        YEAR ( 'Booking'[Month] ) = YEAR ( MAX ( 'Booking'[Month] ) )
            && QUARTER ( 'Booking'[Month] ) = QUARTER ( MAX ( 'Booking'[Month] ) )
    ),
    'Booking'[Total_Cost]
)
    * DISTINCTCOUNT ( 'Booking'[ReservationID] )

 Get the result.

I attach my sample below for your 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.

[demder]

Hello @v-yanjiang-msft ,

It helps me a lot but some parts are still missing. I couldn't get an accurate result to me. I'm adding to my .pbix file here. On page 3 I got the correct result thanks to your solutions. On page 4, you can see another graph. I want to calculate the average total cost per month (Total Cost/Distinct court). Also, I want to calculate the Average total cost per quarterly. (Total Cost/Distinct court). I couldn't calculate correctly the second part I think, I'm doing something wrong.  

https://drive.google.com/file/d/1cyMla1HSTpYiRmsYtcOfmJ4Q5jrZFVQJ/view?usp=sharing 

I couldn't add the file that's why Im adding the drive link.

[v-yanjiang-msft]

Hi @demder ,

Sorry I can't reach your file in the work network. Not sure what's the difference between page3 and page4. Maybe you can clarify it by snapshots if it's not so complex.

Best Regards,
Community Support Team _ kalyj

[demder]

Hello @v-yanjiang-msft ,

Sorry for that, I uploaded the file to my personal account g-drive. I think you can open it now. 

https://drive.google.com/drive/folders/17NcJQ7dIw_JHdeZad4o_E8zdj1ZIKOyz?usp=sharing 

[v-yanjiang-msft]

Hi @demder ,

Sorry I maybe can't figure out the logical in your sample, the relationship of court ID and reservation ID, the problem seems not the same with the original post which needs an average value for all the bars in box. Then if the page 3 works fine why still add a page 4 which seems calculate same things.

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft]

Hi @demder ,

Sorry I still can't open it, maybe it's because of my work network setting.

Best Regards,
Community Support Team _ kalyj

[tamerj1]

@v-yanjiang-msft 
Here you go

[tamerj1]

@demder 
for the title box you can try this code, where the year month should be a column in your date table thank combines the year and the month in any shape. To see the quarter average you can drill up the x-axis.

Total_Cost average =
AVERAGEX (
    VALUES ( 'Date'[YearMonth] ),
    CALCULATE ( SUM ( 'Booking'[Total_Cost] ) )
)

[demder]

@tamerj1 

I created new column with 

YearMonth = FORMAT('Date'[Date],"mmm") & "-" & YEAR('Date'[Date])

After that, I applied your code but the result returned too much small. 

 1.89 K shows up. 

[tamerj1]

@demder 

Please try it with KEEPFILTERS 

Total_Cost average =
AVERAGEX (
    KEEPFILTERS ( VALUES ( 'Date'[YearMonth] ) ),
    CALCULATE ( SUM ( 'Booking'[Total_Cost] ) )
)

[demder]

hello again @tamerj1 , I tried it but it didn't work 😞

[tamerj1]

Hi @demder 

do you have a date table? 

[demder]

Hello @tamerj1 ,

Yes, I have. Also I created, a common one-date table for all values. with this code;

Date =

var startDate = MIN( Booking[Created_At_Date] )

var endDate = MAX( Booking[StartDate] )

RETURN

CALENDAR(  startDate, endDate)