Forum Discussion
Helper IVCalculate nested if in power query
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 |
8 Replies
- FrankAT
Community Champion
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)- AllisonKennedyFrankAT you just need to account for the divide errors and infinity values still I think?
SrikuThanks for the solution. Now I am getting error due to infinity, How is overcome this issue. Please suggest
- 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
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)
- AllisonKennedyYou 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
