Help on Relating 2 Facts Tables with SELECTEDVALUE

[Anonymous]

Hi,

I have 2 fact tables: Timesheet (FINALProjectResourceUtilization_NoHistoricalInvoice) & Invoice (InvoiceRow-NoHistoricalInvoice'[LocalRawRevenueAmount]) connecting to multiple dimension tables.

What I need is to join both fact tables to derive the hours variance & amount variance. 

Invoice table - Hours Invoiced, Hourly Rate, Amount Invoiced

Timesheet table - Hours Produced

Hours Variance = Hours Invoiced - Hours Produced

Amount Produced = (Hours Variance) * Hourly Rate (different rate for different period of time)

For my Amount Produced measured, why is the sumx not showing at the total row? This is the formula I use:

Amount Produced = SUMX(FINALProjectResourceUtilization_NoHistoricalInvoice, FINALProjectResourceUtilization_NoHistoricalInvoice[ResourceHours] * SELECTEDVALUE('InvoiceRow-NoHistoricalInvoice'[LocalRawRevenueAmount]))

Your help is greatly appreciated.

[Anonymous]

Hi @v-yanjiang-msft, thanks for the help. It has been solved. 🙂

[v-yanjiang-msft]

Hi @Anonymous ,

According to your description, you should also use CALCULATE function to return the total value, like this:

Best Regards,
Community Support Team _ kalyj

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

[lbendlin]

Please provide a sample dataset with the involved tables of your datamodel.