How to calculate Sales for the Products with Target using DAX in Power BI

How to show Sales only for the Products with Target available using DAX in Power BI

Scenario:
Suppose we have a Data Model as shown below.

The relationships are as per below:

Bridge_Year = DISTINCT(fact_Target[Year])

The fact_Target table having the Target data defined at Product and Customer level only for the Year 2022, as shown in below sample.

The fact_Sales table having the Target data defined at Product and Customer level for the Years 2021 and 2022, as shown in below sample.

The base Measures are as per below:

Net Sales = SUM(fact_Sales[Net Sales])

Sales Target = SUM(fact_Target[Target Value])

Now we need to Show the [Net Sales] only the for the Products or Customers with Targets available. The Target needs to show by Product, without considering the dim_Date[Year].

We can achieve this Scenario using the following DAX Measure logics:

Sales with Target =

CALCULATE (

    [Net Sales],

    SUMMARIZE(

        fact_Target,

        /* Brige_Year[Year],*/

        dim_Product[ProductKey],

        dim_Customer[CustomerKey]

        )

    )

Sales with Target Flag =

AND( NOT ISBLANK([Sales Target]), NOT ISBLANK([Sales with Target]))*1

We can calculate the Target to show only for the available Target Year using below Logics:

Actual Target Year Flag =

VAR _TargetByYear =

    CALCULATE(DISTINCTCOUNT(fact_Target[ProductKey]), dim_Date)

VAR _Target_Flag = IF(_TargetByYear>0,1,0)

RETURN

_Target_Flag

Actual Target =

CALCULATE (

    [Sales Target],

    FILTER( VALUES(fact_Target[ProductKey]),

    [Actual Target Year Flag]=1

    ))

Result:

[Sales Year] is considered from dim_Date[Year]

[Outlet Name] is considered from dim_Customer

[Brand Name] is considered from dim_Product

[Product SKUs] is considered form dim_Product

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------