Get cost per route within a branch

[zubairs]

Hi Guys,

I am currently creating an analysis where I need to allocate the cost of the entire branch to the number or routes being operated through that branch.

To give you an example, let's say I have branch "A" which consists of 150 routes. So the total Sales Force cost of the organization is broken down into branches. Now what I need to do is create a measure which will give me cost per route per branch.

The branch and the route mapping is on a different table and the cost detail by branch is on a different table.

I already created this measure which works fine only when I have filtered for a particular branch. I want the measure to be dynamic and identify the cost for each route by branch when dragged in a table:

Sales Force (Cost per route) = DIVIDE(

CALCULATE(SUM(Essbase[Total Sales Force]),Essbase[Branch]="Riyadh")*1000,

CALCULATE(DISTINCTCOUNT(SAP_BO_salesdata[Route]),SAP_BO_salesdata[Sub Region Name]="Riyadh"),

0)

[zubairs]

@amitchandak can you please look into this as well. I'll really appreciate it.

[zubairs]

[Jihwan_Kim]

Hi, @zubairs 

I was trying to look into your question, but it will be very much helpful if I can see the sample pbix file or the sample data, if possible.

Thank you very much.