Forum Discussion
Helper IVData Model and DAX Measure for different grain fact tables
Solution SageI will start with this
Total Actuals from Actuals
CALCULATE( SUM( 'Portfolio Actuals'[ExpenseUSDCurrentFYTD] ) ) In this case, there is no need to use Calculate as you are not using for example a FILTER/KEEPFILTERS function.
SUM(Portfolio Actuals'[ExpenseUSDCurrentFYTD] )is enough here
For the first VAR you can filter on two elements from the same table
CALCULATE( [Actuals Total],
KEEPFILTERS('Portfolio Actuals'[Product Enablement Flag] = TRUE &&
'Portfolio Actuals'[Budgetcomponent] = "Actuals")
Hope this helps
Helper IVThanks for and anybody else's help here. Let me simplify.
This image shows the simplified data model:
Note that relationships are based on Project ID. Note also that BudgetComponent is named differently in Project_Financials than in Portfolio Actuals. There are multiple BudgtComponent values per Project ID and additionally, in Portfolio Actuals there are only rows where there are actual costs for that BudgetComponent. Portfolio Actuals is at a week data granularity whereas Project_Financials is at a month date granularity. Portfolio Actuals has granularity for BudgetComponent at the transaction level (ie. every hour charged by every person etc.) whereas Project_Financials is summarised to the BudgetComponent level.
What I need to do is take Project_Financials[BudgetComponent] where the value is "InvOther" and deduct from this amount the corresponding Porfolio Actuals[BudgetComponent] where the value is "Other" AND where the "ProductEnablemen" value is set to "True".