Forum Discussion

dshah12's avatar
dshah12
Helper II
2 years ago
Solved

Regarding the aggregating datasets and modelling

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 noForecast amountMaster applocationFiscal YearClient Role
202562777null2024-2025Master
23455432202562023-2024Phase
Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tutorial Requests
  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    2 years ago

    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!

1 Reply

  • Sahir_Maharaj's avatar
    Sahir_Maharaj
    Super User
    2 years ago

    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!