Calculated column Help

[Anonymous]

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,

[ryan_mayu]

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

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

could you pls provide the sample data and expected output?

[Anonymous]

@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 · Accepted solution]

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

[ryan_mayu]

@Anonymous 

pls see the attachment below