Forum Discussion

kodrutz's avatar
kodrutz
Regular Visitor
8 years ago
Solved

Determine average by month average

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.

 

Need Help
  • kodrutz's avatar
    kodrutz
    8 years ago

    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. :manwink:

3 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    Hi,

     

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

  • kodrutz's avatar
    kodrutz
    Regular Visitor
    8 years ago

    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. :manwink: