Forum Discussion

zubairs's avatar
zubairs
Icon for Helper I rankHelper I
5 years ago
Solved

Cost allocation by branch

Hi Guys,   So this is a probelm that I've not been able to resolve for the longest of time.   So I have two different data sets, one for revenue and one for cost. The data is calculated by geogra...
Need Help
  • AlexisOlson's avatar
    AlexisOlson
    5 years ago

    I think you want to multiply the cost by the fraction of routes in the current context over the total number of routes for that branch.

     

    This should get you close:

    Logistics SAP (Cost per route) =
- [Logistics Cost (KSA)]
    * DIVIDE (
        DISTINCTCOUNT ( Region_to_Van_mapping[Route] ),
        CALCULATE (
            DISTINCTCOUNT ( Region_to_Van_mapping[Route] ),
            REMOVEFILTERS ( Region_to_Van_mapping[Route] )
        ),
        0
    )

     

    In your code, DISTINCTCOUNT is just 1 for each of the routes since the route is part of the filter context for those rows (which is why I remove that filter in the denominator for my DAX).

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

I think you want to multiply the cost by the fraction of routes in the current context over the total number of routes for that branch.

 

This should get you close:

Logistics SAP (Cost per route) =
- [Logistics Cost (KSA)]
    * DIVIDE (
        DISTINCTCOUNT ( Region_to_Van_mapping[Route] ),
        CALCULATE (
            DISTINCTCOUNT ( Region_to_Van_mapping[Route] ),
            REMOVEFILTERS ( Region_to_Van_mapping[Route] )
        ),
        0
    )

 

In your code, DISTINCTCOUNT is just 1 for each of the routes since the route is part of the filter context for those rows (which is why I remove that filter in the denominator for my DAX).