Data Model and DAX Measure for different grain fact tables

[vgeldbr]

I am struggling with what I think is a combination of data model and DAX measure challenge. See data model picture below. Essentially I have tables with:

1. All projects 

2. Financials (plan, forecast and actuals for all projects at month grain)

3. Actuals at week grain but at the individual transaction level

I need to make a calculation where I need to subtract an amount from the Actuals as represented in the Financials table which is represented in the Actuals table as a certain type of invoice. I think I am fine writing the measure but my challenge is the correct way to model the data.

The Financials and the Actuals tables have a many-to-many relationship. I think I can use an inactive relationship in the measure or set up the data model better than I have for this sort of scenario.

Any suggestions (or questions) to get me started down the right route please?

[JoeBarry]

Hi @vgeldbr 

Within the Daily Projects, is the Project ID Unique? remove any relat

If it is, then create a one to many relationship between the Daily Projects and the ProjectID in the Project Financials and the Portfolio Actuals.

The same with DIM Date, Calenderdate with the Date columns in both Project Financials and the Portfolio Actuals. Delete any unnecessary relationships.

The Idea is using the Star Schema https://learn.microsoft.com/en-us/power-bi/guidance/star-schema

If the tables share a Dimension Table, then you can create comparisons with those dimensions on both tables

In visuals and slicers, you would use the columns from the dimension tables, then add the measures to the visuals from the Fact tables, then you can compare

thanks

Joe

If this post helps, then please Accept it as the solution

[vgeldbr]

@JoeBarry thanks.

1. Yes, ProjectID is unique in Daily Projects

2. There is already a 1:* relationship between Daily Projects and Daily ProjectFinancial and between Daily Projects and Actuals (the latter is shown incorrecty in screenshot as I was testing something else)

3. There is 1:* between each of the two date tables and both ProjectFinancial and Actuals

New screenshot should reflect what you suggest. 

I'm still not clear, however, how to write a the measures I need. Currently I have:

1. Total Actuals based on ProjectFinancial 

CALCULATE( SUM( 'Daily_ProjectFinancial'[MTD] ), 'Daily_ProjectFinancial'[Dimension] = "Actuals", KEEPFILTERS( NOT CONTAINSSTRING( 'Daily_ProjectFinancial'[Budgetcomponent], "Total" ) ) )

2. Total Actuals from Actuals

CALCULATE( SUM( 'Portfolio Actuals'[ExpenseUSDCurrentFYTD] ) )

3. I need to deduct certain Actuals from the summarised actuals:

VAR CurrentFY = CALCULATE( MAX( 'DimDate'[FiscalYear] ), ALL( 'DimDate' ), 'Date Periods'[CalendarDate] = TODAY( ) ) VAR PreviousFY_PE = CALCULATE( [Actuals Total], 'Portfolio Actuals'[Product Enablement Flag] = TRUE, KEEPFILTERS('Portfolio Actuals'[Budgetcomponent]) ) VAR Result = SWITCH( TRUE( ), MAX( 'DimDate'[FiscalYear] ) < CurrentFY, [ProjectTotalActuals] - PreviousFY_PE, MAX( 'DimDate'[FiscalYear] ) >= CurrentFY, [ProjectTotalPlan] ) RETURN Result 

Many thanks!

[JoeBarry]

I will to decipher your measures. If I'm not mistaken, you want to show a different total based on a certain date. 

1. Total Actuals based on ProjectFinancial (I'm presuming there is a Total Value you don't want to consider? The  'Daily_ProjectFinancial'[MTD] column, is this the only value column? Do you have a column and a row for each transaction? 

Total Actuals = CALCULATE(SUM( 'Daily_ProjectFinancial'[MTD]), KEEPFILTERS( 'Daily_ProjectFinancial'[Dimension] = "Actuals" &&  'Daily_ProjectFinancial'[Budgetcomponent] <> "Total"

2. CALCULATE( SUM( 'Portfolio Actuals'[ExpenseUSDCurrentFYTD] ) ) Remove the Calculate here

3. If I'm not mistaken, you want to show a different total based on a certain date.  

VAR CurrentFY = What should be the parmeters here? Do you want to see the Max Fiscal year based on Todays Year?

VAR PreviousFY_PE = Is this the Portfolio Actuals where the [Product Enablement Flag] = TRUE?

I don't understand this part of the VAR KEEPFILTERS('Portfolio Actuals'[Budgetcomponent]) 

If you could clear these up for me, maybe I can help further

[vgeldbr]

You can ignore the date element of the final measure. All it is doing is taking total actuals for previous fiscal year but taking total actuals - PE for the current fiscal year. The relevant portion is:

VAR PreviousFY_PE = CALCULATE( [Actuals Total], 
'Portfolio Actuals'[Product Enablement Flag] = TRUE, 
KEEPFILTERS('Portfolio Actuals'[Budgetcomponent]) 
) 
VAR Result = SWITCH( TRUE( ), 
[ProjectTotalActuals] - PreviousFY_PE 
) 
RETURN 
Result 

1. Total Actuals based on Project_Financial has a $ value but there is a another column [Dimension] which indicates if the line relates to Actuals, Forecast or Plan and also a column [Budgetcomponent] that indicates if it is one of multiple cost types, a sub-total for those cost type or the total for the month across all cost types. I don't control this data unfortunately. The measure selects only [Dimension] = "Actuals" and the detail lines ignoring all sub-totals and totals. The result is a row for each project for each month for each cost type. The measure works fine across many reports today.

2. Can you help me understand why I should remove the calculate? This measure is used in other constexts.

[JoeBarry]

I 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

[vgeldbr]

Thanks 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".