Forum Discussion

BartzD01's avatar
BartzD01
Regular Visitor
7 years ago
Solved

Proportionally allocation

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...

 

Need Help
  • BartzD01's avatar
    BartzD01
    7 years ago

    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")))

3 Replies

  • dax's avatar
    dax
    Community Support
    7 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    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's avatar
      BartzD01
      Regular Visitor
      7 years ago

      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")))