Using value from another linked table in a measure

[Anonymous]

Hi Guys. I'm stuck really bad in this one. I have 2 tables : 1. Facts table 2. Dimesnion table. Following image depicts the relationship established between the 2. The table at the top is the Facts table. "OI Owrking Days" is the dimension table.

I wish to create a multiline chart which will have the "FY" field in the legend well, Month field in Axis well and a measure in the Values well. The measure is supposed to calculate Per day amount so the formula for the same would be : SUM(Amount)/working days. I need to have the working days to be fetched from dimension table for each month that is plotted on the X-Axis. Following is what I have tried so far :

Amt Per Day =

var AmtPerDay = [Total Amount] / FILTER( 'Facts Table', RELATED( 'Dimensions Table'[WORKING_DAYS] ) )

RETURN

AmtPerDay

Note : Both the tables are fetched using DirectQuery mode. 😐
Any help is appreciated. 

[amitchandak]

@Anonymous , Try like

Amt Per Day =
var AmtPerDay = [Total Amount] / Sumx( 'Facts Table', RELATED( 'Dimensions Table'[WORKING_DAYS] ) )
RETURN
AmtPerDay

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[Anonymous]

I'm afraid that did not work @amitchandak. Check out the image below :

This is how the line chart shoud look like. But it looks like :

I am not sure how to get the desired results. Been trying a lot of different functions since last 2 days but nothing has worked thus far. 😞

[v-deddai1-msft]

Hi @Anonymous ，

Would you please use the following measure:

Amt Per Day =
var AmtPerDay = SUMX('Facts Table', [Total Amount]/RELATED( 'Dimensions Table'[WORKING_DAYS] ) )
RETURN
AmtPerDay

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai