Allocate value to selected categories based on measure

[tweidner]

Hi everybody

today I'm trying to achieve the following:

What I'm trying to achieve is to allocate the cost from Plant 'A', based on Days Online, to Plant 'B' and 'C'.

Should look like this:

Thanks & Rgds
Tobias

[MohammadLoran25]

Follow these steps to achieve your desired result.

1-Create 2 Same Tables which contains your plants. (PlantTable1 and PlanTable2).

PlantTable1 includes Plant1 Column 

PlantTable2 includes Plant2 Column

2-Assume your main table name is PlantCost (including Columns Plant, Costs and DaysOnline). Then:

Create relationships between PlantCost and PlantTable1 (on Plant and Plant1)

PlantCost and PlantTable2 (on Plant and Plant2)

3-CREATE measures Below:

SumOfCosts = SUM(PlantCost[Costs])

-------------------------------------------------

SumOfDaysOnline = SUM(PlantCost[DaysOnline])

-------------------------------------------------

SELECTEDCOST1 =

CALCULATE(

SUMX(ALLSELECTED(PlantTable1),[SumOfCosts]),

CROSSFILTER(PlantTable2[Plant2],PlantCost[Plant],NONE)

)

-----------------------------------------------

SELECTEDDaysOnline2 = CALCULATE(

SUMX(ALLSELECTED(PlantTable2),[SumOfDaysOnline]),

CROSSFILTER(PlantTable1[Plant1],PlantCost[Plant],NONE)

)

-----------------------------------------------------

4-Finally, your AllocatedCost Measure is as below:

AllocatedCost =

VAR X=[SELECTEDCOST1]

RETURN

CALCULATE(

    DIVIDE(X*[SumOfDaysOnline],[SELECTEDDaysOnline2])

+[SumOfCosts]

,

CROSSFILTER(PlantCost[Plant],PlantTable1[Plant1],NONE)

)

For the sample case u used on your post, you need to

select "A" on a slicer for Plant1 from PlantTable1 

AND 

select "B" and "C" on a slicer for Plant2 from PlantTable2.

Then by putting Plant2 Column and [AllocatedCost] measure, your result is what you want. 

If this answer solves your problem, please mark it as an accepted solution so the others would find what they need easier.
Regards,
Loran

[MohammadLoran25]

Hi @tweidner 

You only have these 3 categories or it is a sample data?

I mean always you want to B and C from A?

[tweidner]

Hi @MohammadLoran25 

in my dataset I've like 100 entries in 'Plant' which are allocated to 25 countries.

I didn't mention that because I assume I would be able to solve it, if example scenario is solved.

Thanks for your feedback!
Rgds
T