Dax measure help

SilentEagle 

Here's a concise, clear, and effective DAX measure to allocate the costs of your supporting entity to the respective countries, based on their net sales contributions:

Allocated Costs Measure:

Allocated Fixed Costs EUR =
VAR CurrentRegion = MAX('CostTable'[Region])  // e.g., "Asia"
VAR CurrentCountry = MAX('CostTable'[Country])
VAR IsSupportEntity = MAX('CostTable'[IsSupportEntity]) // TRUE/FALSE

// Total Fixed Costs for the Region's Supporting Entity (Coordination Unit)
VAR SupportEntityCost =
    CALCULATE(
        [Dynamic Fixed Cost EUR],
        FILTER(
            ALL('CostTable'),
            'CostTable'[Region] = CurrentRegion &&
            'CostTable'[IsSupportEntity] = TRUE()
        )
    )

// Net Sales Contribution per Country within the Region
VAR CountryNetSales =
    CALCULATE(
        SUM('SalesTable'[NetSales]),
        FILTER(
            ALL('SalesTable'),
            'SalesTable'[Region] = CurrentRegion &&
            'SalesTable'[IsSupportEntity] = FALSE()
        )
    )

VAR TotalRegionNetSales =
    CALCULATE(
        SUM('SalesTable'[NetSales]),
        FILTER(
            ALL('SalesTable'),
            'SalesTable'[Region] = CurrentRegion &&
            'SalesTable'[IsSupportEntity] = FALSE()
        )
    )

// Allocation Ratio
VAR AllocationRatio =
    DIVIDE(CountryNetSales, TotalRegionNetSales, 0)

// Final allocated costs
VAR AllocatedCost =
    IF(
        IsSupportEntity,
        BLANK(),  // Exclude coordination units from showing allocated costs
        [Dynamic Fixed Cost EUR] + (SupportEntityCost * AllocationRatio)
    )

RETURN
    AllocatedCost

How it works:
It calculates the total supporting entity cost for each region.
Distributes this cost among the countries based on their net sales contributions.
Adds the allocated portion to the country's existing dynamic fixed cost measure.
Coordination units do not display the allocated cost.
This measure directly leverages your existing [Dynamic Fixed Cost EUR] measure, ensuring consistency.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

Hi thank you so much! I changed up the formula a bit because I don`t have the region, country columns in the cost table only the entities in one column. This partially works but the issue is I cant see the allocated cost in my power pivot when using the entity column from the mapping table. I can only see it when I use the entity column from the cost table as a filter. Both of the cost and net sales tables have a relationship with the mapping table so I dont understand why I cant see the values (all of the other dynamic measures work with my filters and slicers). If I change the first part of the formula to lookup entity from mapping table the measure doesn`t return any values in the pivot but if I double click on "JPN" as an example I can see there is cost allocated. Am I doing something wrong in mapping the entities?

Allocated cost:=VAR CurrentEntity = MAX('BDG 24 25'[Entity])

VAR IsTargetEntity = CurrentEntity IN {"SGP", "JPN"}

VAR ASC_Cost =
CALCULATE(
[BDG YTD],
'BDG 24 25'[Entity] = "ASC"
)
VAR EntityNetSales =
CALCULATE(
SUM(NetSales[Value]),
NetSales[Scenario] = "BDG",
NetSales[Entity] = CurrentEntity
)

VAR TotalTargetNetSales =
CALCULATE(
SUM(NetSales[Value]),
NetSales[Scenario] = "BDG",
NetSales[Entity] IN {"SGP", "JPN"}

)
VAR AllocationRatio =
DIVIDE(EntityNetSales, TotalTargetNetSales, 0)

VAR AllocatedAmount =
IF(
IsTargetEntity,
ASC_Cost * AllocationRatio,
0
)

RETURN
AllocatedAmount