Determine average by month average

[kodrutz]

Hy guys,

I am pretty new to DAX and I`m struggling with some of my data.

I have the following table:

The table contains records from 2013 up to now.

I need to determine the average mileage per month per vehicle.

SUM of ( ( Total KMs in month ) / ( Number of distinct vehicles in month ) ) / Total number of months.

I managed to determine the values using ca new DAX table and then creating a measure based on that, but the values don`t filter when I select the Brand or the Type.

The ideea is that I need to be able to see the average mileage of the entire fleet, but also slice the data by Brand or Type.

I would really appreciate some help on this.

Thanks.

[kodrutz · Accepted solution]

Hi Angelia,

Thanks a lot for your help.

It wasn’t exactly the solution to my problem but you kind of pointed me in the right direction.

In the end I figured out how to obtain the results with a combination of measures.

Ashish, thanks for taking the time. 

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  Also, show the expected result there.

[v-huizhn-msft]

Hi @kodrutz,

You use the brand column to calculate the distinct vehicles? If it is, you should create a month column by creating calculated column.

Month=Format(Table[Date],"YYYY-MMM")

Then you can create a measure using the formula below.

Average =
DIVIDE (
    CALCULATE ( SUM ( Table[mile age] ), ALLEXCEPT ( Table, Table[month] ) ),
    CALCULATE ( DISTINCTCOUNT ( Table[brand] ), ALLEXCEPT ( Table, Table[month] ) )
)
    / CALCULATE ( DISTINCTCOUNT ( Table[month] ), ALL ( Table ) )

If this still can't resolve your issue, please give reponse for further analysis. And you'd better share the sample table in table format rather than screenshot.

Best Regards,
Angelia