Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculated column Help

Hello Power Users,

 

I'm trying to calculated the following logic.  If the category column has "A" and "B" and i would like split the Value column By For "A" its 0.4*Value and for "B" its 0.2*Value and all others its should be 0.4*Value/CountOfCategory(Exclude A&B).

 

 

For example For Booking Number "123" , It has both Catergory "A" & "B" then The value for Category "A" = 100*0.4 and For "B"= 100*.02 and all others = 100*0.4/Count of Category Column(Excluding A&B) i.e 2

For example For Booking Number "234" , It has only Catergory "A" then The value for Category "A" = 100*0.4 all others = 100*0.6/Count of Category Column(Excluding A) i.e 2

For example For Booking Number "345" , It has only Catergory "B" then The value for Category "B" = 100*0.2 all others = 100*0.8/Count of Category Column(Excluding B) i.e 4

 

If We don't have A & B then split them equally.

 

Please help

 

Thanks,

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, Anonymous 

    Thank you for your detailed explanation for your need .

    Here are the steps you can refer to :

    (1) My test is the same as yours.

    (2)We can click "New Column" and enter :

    Output = 
var _number = [Booking Number] 
var _category = DISTINCT(SELECTCOLUMNS( FILTER('Table','Table'[Booking Number]=_number) , "Category" , [Category]))
var _exclude_count =COUNTROWS( EXCEPT( _category , {"A","B"}))
var _cate = [Category]
var _value =MAXX(FILTER( 'Table' , 'Table'[Booking Number] =_number ) , [Value])
var _percentage =INTERSECT(_category , {"A","B"})
var _per_value = IF( COUNTROWS( _percentage) =2 , 0.4 , IF( COUNTROWS(_percentage) =1 && COUNTROWS(INTERSECT({"A"},_percentage))=1 , 0.6 , 0.8))
 return
 IF([Expcnt] > 0 , [Expcnt] ,   IF(COUNTROWS(_category)<=3 , [Value1] ,   IF( _cate ="A", _value*0.4 ,IF( _cate ="B" , _value*0.2 ,DIVIDE( _per_value * _value,_exclude_count  )))))

    (3)Then we will meet your need:

     

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

6 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Super User
    3 years ago

    Anonymous 

    i think the value for 345's CDEF should be 40, 200*0.8/4

    pls try this

    Column = 
var _a=maxx(FILTER('Table','Table'[booking number]=EARLIER('Table'[booking number])&&'Table'[category]="A"),'Table'[VALUE])
var _b=maxx(FILTER('Table','Table'[booking number]=EARLIER('Table'[booking number])&&'Table'[category]="B"),'Table'[VALUE])
var _value=maxx(FILTER('Table','Table'[booking number]=EARLIER('Table'[booking number])&&'Table'[VALUE]<>0),'Table'[VALUE])
var _num=CALCULATE(COUNTROWS('Table'),FILTER('Table','Table'[booking number]=EARLIER('Table'[booking number]) &&  'Table'[category]<>"A" && 'Table'[category]<>"B"))
return if(ISBLANK(_a)&&ISBLANK(_b),_value/_num,if(ISBLANK(_a)&&not(ISBLANK(_b)),if('Table'[category]="B",_value*0.2,_value*0.8/_num),if(not(ISBLANK(_a))&&ISBLANK(_b),if('Table'[category]="A",_value*0.4,_value*0.6/_num),if('Table'[category]="A",_value*0.4,if('Table'[category]="B",_value*0.2,_value*0.4/_num)))))

    pls see the attachment below

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      ryan_mayu Its working fine and i would like add 2 more conditions to the logic. I have one other column contains Value1 so the first If should be check if the Value 1 is greater than in the new column we have to have that Value 1 records if the Value 1 =0 and then do the calculation and second if should be If the count of categories is ❤️ then split them the value equally and If its  >3 then we have to use logic. How to implement this. 

      • ryan_mayu's avatar
        ryan_mayu
        Super User
        3 years ago

        could you pls provide the sample data and expected output?