Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I am new to Dax and my details as follows. My formula works well for each vehicle running cost per hour but i am not able to get the figure like civic when drill up to Model like civic cost per running hour will be 1250/540 = 2.31 (highlighted in Yellow).
Solved! Go to Solution.
Hey,
my solution assumes you have two tables (if not you should consider to change your model, due the different granularity of your measures (running hour and Amount)
Both tables are related: Vehicle (one to many) Expense
Now I create a measure (associated to the expense table):
Cost per running hour = CALCULATE( DIVIDE(SUM('Expense'[Amount]), CALCULATE(SUM(Vehicle[Vehicle running hour]), RELATEDTABLE(Vehicle) ) ) )
Using the RELATEDTABLE(...) function the value of "vehicle running hour" is pulled from the one side to the many side using the relationship.
Here you can find my simplified example for the solution I described above.
Hope this helps
Hey,
my solution assumes you have two tables (if not you should consider to change your model, due the different granularity of your measures (running hour and Amount)
Both tables are related: Vehicle (one to many) Expense
Now I create a measure (associated to the expense table):
Cost per running hour = CALCULATE( DIVIDE(SUM('Expense'[Amount]), CALCULATE(SUM(Vehicle[Vehicle running hour]), RELATEDTABLE(Vehicle) ) ) )
Using the RELATEDTABLE(...) function the value of "vehicle running hour" is pulled from the one side to the many side using the relationship.
Here you can find my simplified example for the solution I described above.
Hope this helps
It works well with your formula when i filtered only one month of data from sql view for both table. But when i tried with two months and the calculation is wrong again.
you can download from here for the pbix file that i have created.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |