Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
seanvooi
New Member

dax formula

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). 

 Sample.JPG

1 ACCEPTED SOLUTION
TomMartens
Super User
Super User

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)

  1. Vehicle (VehiclNo, ..., running hour)
  2. Expense (VehicleNo, type of expense, 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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

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)

  1. Vehicle (VehiclNo, ..., running hour)
  2. Expense (VehicleNo, type of expense, 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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

 

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. 

 

 

 test2.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.