Proportionally allocation

[BartzD01]

Table name "Analysis"

I want to allocate proportionally in one column the amount 50 at Business "B" sales and the anount 100 at Business "W".

So it will be like row be row for business B:

100/(100+150+200+300)*50

150/(100+150+200+300)*50  and so on ...

and for business W:

100/(100+150+200+400)*100

150/(100+150+200+400)*100 and so on...

[Anonymous]

Try this "Calculated Column"

Allocation =
DIVIDE (
    Analysis[Sales],
    SUMX (
        FILTER ( ALL ( Analysis ), Analysis[Business] = EARLIER ( Analysis[Business] ) ),
        Analysis[Sales]
    ),
    0
)
    * SWITCH ( Analysis[Business], "B", 50, "W", 100, 0 )

[BartzD01 · Accepted solution]

Thank you all for your help.

After long searcing and advised by your solution I wrote the following which works based on me needs.

Column =
Business[Sales]/SWITCH(TRUE(),Business[Business]="B",CALCULATE(SUM(Business[Sales]),FILTER(Business,Business]="B")),

Business[Business]="W",CALCULATE(SUM(Business[Sales]),FILTER(Business,[Business]="W")))

[dax]

Hi BartzD01,
According to your description, it seems that you want to get result like below

You could try to use below measure to see whether it works or not

Measure = VAR DC = IF(MAX(Table1[Business])="B", 50, 100) return sum(Table1[Sales])/CALCULATE(sum(Table1[Sales]), ALLEXCEPT(Table1,Table1[Business]))*dc

Best Regards,
Zoe Zhi