Regarding the aggregating datasets and modelling

[dshah12]

I want the values of Phase to be merged into the Master so the file 20256 should have value 2777+5432 =8209 but when I filter let's say for year 2023-2024 it should give me 5432 as the amount.

Not able to understand how to perform any help highly appreciated !

Application no	Forecast amount	Master applocation	Fiscal Year	Client Role
20256	2777	null	2024-2025	Master
2345	5432	20256	2023-2024	Phase

[Sahir_Maharaj · Accepted solution]

Hello @dshah12,

Can you please try this approach:

1. Create a Calculated Column

Total Forecast Amount = 
VAR CurrentApp = 'Table'[Application no]
RETURN
CALCULATE(
    SUM('Table'[Forecast amount]),
    FILTER(
        'Table',
        'Table'[Application no] = CurrentApp || 'Table'[Master application] = CurrentApp
    )
)

2. Create Measures for Filtering

Filtered Forecast Amount = 
VAR CurrentApp = MAX('Table'[Application no])
RETURN
CALCULATE(
    SUM('Table'[Forecast amount]),
    FILTER(
        'Table',
        ('Table'[Application no] = CurrentApp || 'Table'[Master application] = CurrentApp) &&
        'Table'[Fiscal Year] = SELECTEDVALUE('Table'[Fiscal Year])
    )
)

Total Forecast Amount = 
VAR CurrentApp = MAX('Table'[Application no])
RETURN
CALCULATE(
    SUM('Table'[Forecast amount]),
    FILTER(
        'Table',
        'Table'[Application no] = CurrentApp || 'Table'[Master application] = CurrentApp
    )
)

Hope this helps!