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