Dax measure help

[SilentEagle]

Hi there,

I`m stuck on a dax measure so hoping someone can help me out.

I`m working with fixed costs by different country/entity in local currency/FY/scenarios and I`ve written a dynamic measure to get the cost converted in EUR at the fx of the YTD period. Now I want to show the cost by country but the issue is some of the cost holding entities are not countries but supporting entites (I call them coordination units) so I would like to allocate the cost of these supporting entities to the respective countries. In the attached example I have  a supporting entity for Asia and the driver to split the cost is the net sales contribution of the countries in Asia (in this example SGP and JPN, so SGP and JPN need to share the cost of the supporting entity). I need help writing this measure by using the existing dynamic measure for fixed costs. 

Thanks in advance!

[SilentEagle]

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

[v-menakakota]

Hi @SilentEagle ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[SilentEagle]

Hi @v-menakakota,

Thanks for your help and apologies for the delay. I tried your suggestion with using "Related" function but I get an error saying that the entity column doesn`t either doesn`t exist or doesn`t have a relationship to any table availabe in the current context. I also tried to use the entity column of the mapping table instead of the cost (BDG) table but I got the same error. Both cost and net sales tables have a relationship on the entity with the entity mapping table so not sure why it doesnt work. 

[v-menakakota]

Hi @SilentEagle ,

Can you please provide the details of model view or screenshot it would be easy to figure out.

Thank you.

[SilentEagle]

Hi @v-menakakota ,

Sure, please see below the relationsips and the pivot output. For some reason the % split that is calculated in this sample file through the allocated cost is wrong but in my actual working file it`s correct so you can ignore that. In the 2nd screenshot you can see that with the last shared formula I can only see the allocation if I use the BDG entity column (even though the % is wrong here). If I replace the entity column from BDG table to mapping table in the measure now I can see the allocated cost but it`s not split out, it`s duplicated instead. Also the measure where I tried the previously suggested "related" function is in error.

Many thanks

 

 

[v-menakakota]

Hi @SilentEagle ,

It can be due to uni directional filter. If we can make the relation between Mapping and cost table as bi-directional then will be able to see the values i guess. Can you try it ones and check.

Thank you and Regards,
Menaka.

[v-menakakota]

Hi @SilentEagle ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-menakakota]

Hi @SilentEagle ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[SilentEagle]

Hi @v-menakakota ,

No, I havent resolved it yet. I believe the bi-directional relationship only works in Power Bi not Excel. I`ve tried multiple measures and filters but still no luck.

Thanks for your help though!

[v-menakakota]

Hi @SilentEagle ,

Can you please confirm whether you have resolved issue. If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. This will be helpful for other community members who have similar problems to solve it faster. 

If we don’t hear back, we’ll go ahead and close this thread.Should you need further assistance in the future, we encourage you to reach out via the Microsoft Fabric Community Forum and create a new thread. We’ll be happy to help.

[v-menakakota]

Hi @SilentEagle ,

Just checking in to see if you're using Power BI  or Excel for this scenario. This detail will help us tailor the guidance more accurately to your use case.Looking forward to your reply so we can assist you further. If the query is resolved, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you

[v-menakakota · Accepted solution]

Hi @SilentEagle ,

Can you please confirm what you are using Power BI or Excel?

[v-menakakota]

Hi @SilentEagle ,
Thank you for reaching out to us on the Microsoft Fabric Community Forum.

Using related function would be a good way. If the mapping table is on one side you can use the Related function on the entity.

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",
RELATED('BDG 24 25'[Entity]) IN {"SGP", "JPN"}
)

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

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

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

RETURN
AllocatedAmount

If this post was helpful, please give us Kudos and consider marking Accept as solution to assist other members in finding it more easily.

[VahidDM]

@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