Goals per Customer portfolio

[Rai_BI]

Hi friends,

I have a table with the customer portfolio called "Dim_Customer_portfolio" and in this table there are three different types of customer portfolios which are 1, 2 and 3. As you can see in the attached image.

Each portfolio has a goal that is in the "Fact_Goals" goal table

I need a single DAX measure that calculates the target for each portfolio.
If the scope is "Portfolio1 Name", then calculate the goals of all portfolios of type "Portfolio1 Name".

If the scope is "Portfolio2 Name", then calculate the goals of all portfolios of type "Portfolio2 Name".

If the scope is "Portfolio3 Name", then calculate the goals of all portfolios of type "Portfolio3 Name".

I only use the information from the 'Dim_Customer_portfolio" table in the visuals.

 

[mtayyab07]

Please try this measure:

CalculatePortfolioGoal = 
SUMX(
    VALUES('Dim_Customer_portfolio'[Customer ID]),
    CALCULATE(
        SUM('Fact_Goals'[Goal]),
        FILTER(
            'Fact_Goals',
            'Fact_Goals'[Portfolio ID] IN VALUES('Dim_Customer_portfolio'[Portfolio1 ID])
            || 'Fact_Goals'[Portfolio ID] IN VALUES('Dim_Customer_portfolio'[Portfolio2 ID])
            || 'Fact_Goals'[Portfolio ID] IN VALUES('Dim_Customer_portfolio'[Portfolio3 ID])
        )
    )
)