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