Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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.
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.
Hi,
Share the link from where i can download your file. Also, show the expected result there.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
94 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |