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

     

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
Icon for Super User rankSuper User
3 years ago

could you pls provide the sample data and expected output?

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    ryan_mayu Thank you for the response. Here is the sample data and expected Output.

    The first rule is to check if the expcnt column has value or 0. if its >0 then the logic for the calculated column is same values(Expcnt value). 
    Second condition is if expcnt =0 and if Countof Categories<3 then use Value1 and  expcnt =0 and if Countof Categories> 3 then same logic you provided.

     

    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 3 categories so we have  to use Value1 Column

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

    For example For Booking Number "456" , The EXPCNT has values so we have to use those values as output.

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

     

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

     

    Thanks

     

    • v-yueyunzh-msft's avatar
      v-yueyunzh-msft
      Icon for Community Support rankCommunity Support
      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