Calculate nested if in power query

[Sriku]

Hi,

I need to calculate Percentage in power query. If Country = India then (Available - (DowntimeHrs/7.5)- Required)/Required ) and for other country (Available - (DowntimeHrs/6.5)- Required)/Required )

IFERROR(IF($A2="India",($B2-($C2/7.5)-$D2)/$D2,($B2-($C2/6.5)-$D2)/$D2),"")

Country	Available	DowntimeHrs	Required	Percentage%
Philippines	15.00	0.00	18.00	-16.7%
India	14.00	2.25	14.00	-2.1%
Japan	42.00	0.00	44.00	-4.5%
India	27.00	0.00	31.00	-12.9%
India	5.00	0.00	5.00	0.0%
India	20.00	0.00	20.00	0.0%
India	1.00	0.00	1.00	0.0%
India	3.50	0.00	5.00	-30.0%
India	0.50	0.00	1.00	-50.0%
India	1.00	0.00	1.00	0.0%
India	0.50	0.00	1.00	-50.0%
India	1.00	0.00	1.00	0.0%
India	2.00	0.00	2.00	0.0%
India	4.00	0.00	4.00	0.0%
India	6.00	0.00	6.00	0.0%
India	12.00	0.00	12.00	0.0%
India	14.00	0.00	16.00	-12.5%
India	5.00	0.00	5.00	0.0%
India	5.00	4.00	4.40	1.5%
India	3.00	0.00	1.50	100.0%
India	12.00	38.57	6.00	14.3%
India	9.00	7.00	1.00	706.7%
Japan	7.00	12.14	2.26	127.1%
Japan	11.00	0.00	1.17	840.2%
Japan	2.00	0.26	0.00
Japan	4.00	0.26	0.81	388.9%
Japan	2.00	1.05	3.97	-53.7%
Japan	4.00	0.00	0.53	654.7%
Japan	6.00	38.57	1.52	-95.6%
Japan	3.00	0.00	4.33	-30.7%
Japan	28.50	0.00	0.00
Japan	14.00	0.00	1.16	1106.9%
Japan	11.50	0.00	3.00	283.3%
Japan	15.50		3.00	416.7%
Japan	4.00	10.00	0.00
Japan	148.00	0.00	3.00	4833.3%
Japan	2.00	0.00	29.00	-93.1%
Japan	8.00	0.00	2.00	300.0%
Japan	4.60	0.00	2.00	130.0%
Japan	0.40	0.00	26.00	-98.5%
Philippines	72.00	0.00	69.00	4.3%
Philippines	107.00	0.00	5.90	1713.6%
Philippines	10.00	0.48	2.00	396.3%
Philippines	2.00	0.00
Philippines	1.00	0.00

[AllisonKennedy]

You can edit the formula bar for the built in conditional column. First add a conditional column like this: 

Then edit the formula bar so it looks like this: 

Otherwise, click the button to create a new CUSTOM COLUMN in the Add Column tab, and paste this formula into the box: 

if [Required]= 0 then null else if [Country] = "India" then Value.Divide([Available] - [DowntimeHrs]/7.5 - [Required],[Required]) else Value.Divide([Available] - [DowntimeHrs]/6.5 - [Required],[Required])

@Sriku  UPDATE: Don't forget to set data type to number when you're done too.

[FrankAT]

Hi @AllisonKennedy 

thx for pointing to the exception, your formula is 👍.

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[FrankAT]

Hi @Sriku 

take a look at the following solution:

// Table
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rZXLasMwEEV/JRi6G4NGb31Cu+reZBFooYYSAv1/qCxZtu/Y7iJ0ZRSdzOPeGXsYuvev8Xt8PMb7509HHTtSKj9VfXCsz549hZfuSkP3ev8Yb9OVrVeatNsce01cwbfb43bPv1gNIW0DLTmMqAOAhltuTQlJLHI9iXgKsM1RdIItn1CG3D5nb3acAq714J7M+r/R0Al9Qln064TyQPkzbTEln+VkTMp+9d49472D4i3ZKouTnqJqRWtWp12YSC6s3eaiDYKpXgSwIahledpOhEUOtnWHfDkGuTwsjeUpf7SKNIKLsCXSjF9wEQUSubQU23aJUDmzKxqlUGbOyCbQsjypZpLGWcl5FC/LrKeAyZFHEO3I4pp5xURAHWErDpuVA0VcFObsRdpJvI02F6GjaeYuoKvgZaUs76yd8/LfxUV81c3RotklFSs7D1ifjJwUjNgGdp3ltT6/59jsOFV3pnFtIVNsS4QfjoCF+rSYeESzwne9o1TKCGzaUMg/NN7GTXvJH8fXQvxmgAh6RF1/AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Country = _t, Available = _t, DowntimeHrs = _t, Required = _t, #"Percentage%" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Available", type number}, {"DowntimeHrs", type number}, {"Required", type number}, {"Percentage%", Percentage.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Percentage%"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns", "Percentage", 
each if [Country] = "India" 
then ([Available] - ([DowntimeHrs]/7.5) - [Required])/[Required]  
else ([Available] - ([DowntimeHrs]/6.5) - [Required])/[Required]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"Percentage", Percentage.Type}})
in
    #"Changed Type1"

With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)

[AllisonKennedy]

@FrankAT you just need to account for the divide errors and infinity values still I think?

[Sriku]

Thanks for the solution. Now I am getting error due to infinity, How is overcome this issue.  Please suggest 

[AllisonKennedy]

@Sriku Which solution are you referring to?

This formula checks if the [Required] column is 0 first, and if it is then puts null as value, which should remove the infinity values. If it's not working, please share a screenshot of what you see and which rows have infinity and what formula you're using.

if [Required]= 0 then null else if [Country] = "India" then Value.Divide([Available] - [DowntimeHrs]/7.5 - [Required],[Required]) else Value.Divide([Available] - [DowntimeHrs]/6.5 - [Required],[Required])

[AllisonKennedy]

You should be able to do this in Power Query using the built in functionality.

Click the Add Column tab, then choose 'conditional column'.

You can add a row to add more conditions, but your formula only needs one condition - change the type from value to column and choose the [Country] column. Then type India in the box.

Then choose column for the 'then' box. and pick the 'Available' column. Do the same for the 'else' box.

Then turn on your formula bar (click on View tab in Power Query) and manually edit the formula to provide the conditions using the same syntax and pattern already put in place for you.

[Sriku]

I am a learner to powerbi can you please help me in sharing in screen shot or share the logic applied in Powerbi