Answered by:
How to calculate tax amount dynamically.
Question

Hi there,
I want to calculate tax amount on salary based on following criteria
For Example
Salary Amount 11000
Criteria
3000 0%
6000 5%
9000 7.5%
12000 12%
1st 3k will be 0% then 3k will be 5% then 7.5% and remaining 2k out of 11k will be 12% and then sum all to calculate tax amount.
Thanks
Hayder Alee
Answers

Hi Haider!
Suppose, you have such Criteria and Data tables:
Then you may use following code:
// Criteria let Source = Excel.CurrentWorkbook(){[Name="Criteria"]}[Content], types = Table.TransformColumnTypes(Source,{{"Salary", Int64.Type}, {"Tax rate", Percentage.Type}}) in types // Taxes let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], add = Table.AddColumn(Source, "tax", (z) => Table.Distinct(Table.ReplaceValue(Criteria, 0, each z[Salary Amount],(a,b,c) => if a<c then a else c, {"Salary"}), "Salary")), tax = Table.ReplaceValue(add, 0, each [i = Table.AddIndexColumn([tax], "i", 0, 1), j = Table.AddIndexColumn(i, "j", 1, 1), join = Table.NestedJoin(j,{"i"},j,{"j"},"range"), replace = Table.ReplaceValue(join,0,each [Salary],(a,b,c)=> c  (try a[Salary]{0} otherwise 0),{"range"}), tax = Table.AddColumn(replace, "tax", each [range]*[Tax rate])][tax], (a,b,c)=>List.Sum(c[tax]), {"tax"}) in tax
But keep in mind: if, for example, Salary Amount = 15000, then for last 3k (15000  12000) tax will be equal zero, since there are no criterias for salary > 12000. So, if this scenario is unwanted, you should indicate knowingly large number for last criteria (for instance, 12 000 000 instead of 12 000).
 Edited by Aleksei Zhigulin Sunday, August 25, 2019 5:32 PM
 Marked as answer by Haider Alee Tuesday, August 27, 2019 6:25 AM

I believe this article should help you:
https://www.powerquery.training/portfolio/vlookuptrueinpowerquery/
 Marked as answer by Haider Alee Wednesday, August 28, 2019 1:10 PM

another approach, without using Table.Replace.Value that Aleksei likes so much (and so do I)
I also amended the Criteria table that I found confusing: the 12,000 limit is not useful at all. I replaced upper bound limit by lower bound limit
Benefit: it manages all salaries even above 12,000
let TaxRate_tbl = #table( type table[lower bound limit=Int64.Type, rate=Percentage.Type], { {0,0}, {3000,0.05}, {6000,0.075}, {9000,0.12} //,{12000,0.12} //no need, duplicate criteria } ), Salary_tbl = #table( type table[id=Int64.Type, salary=Currency.Type], { {1,11000}, {2,2000}, {3,6000}, {4,20000} } ), TaxRate2_tbl = let Index.0 = Table.AddIndexColumn(TaxRate_tbl, "index.0",0,1), Index.1 = Table.AddIndexColumn(Index.0, "index.1", 1, 1), MergeIndex = Table.NestedJoin(Index.1, {"index.1"}, Index.1, {"index.0"}, "TOTO", JoinKind.LeftOuter), ExpandIndex = Table.ExpandTableColumn(MergeIndex, "TOTO", {"lower bound limit"}, {"upper bound limit"}) in ExpandIndex [[lower bound limit],[upper bound limit],[rate]], AddTax = Table.AddColumn(Salary_tbl, "tax", (S)=> List.Sum( Table.AddColumn(TaxRate2_tbl, "tax for each range", (T)=> T[rate] * ( if S[salary]<T[lower bound limit] then 0 else if T[upper bound limit]=null or S[salary]<T[upper bound limit] then S[salary]  T[lower bound limit] else T[upper bound limit]  T[lower bound limit] ) ) [tax for each range] ), Currency.Type ) in AddTax
 Edited by anthony34 Tuesday, August 27, 2019 9:12 AM
 Marked as answer by Haider Alee Tuesday, August 27, 2019 4:21 PM
All replies

Hi Haider!
Suppose, you have such Criteria and Data tables:
Then you may use following code:
// Criteria let Source = Excel.CurrentWorkbook(){[Name="Criteria"]}[Content], types = Table.TransformColumnTypes(Source,{{"Salary", Int64.Type}, {"Tax rate", Percentage.Type}}) in types // Taxes let Source = Excel.CurrentWorkbook(){[Name="Data"]}[Content], add = Table.AddColumn(Source, "tax", (z) => Table.Distinct(Table.ReplaceValue(Criteria, 0, each z[Salary Amount],(a,b,c) => if a<c then a else c, {"Salary"}), "Salary")), tax = Table.ReplaceValue(add, 0, each [i = Table.AddIndexColumn([tax], "i", 0, 1), j = Table.AddIndexColumn(i, "j", 1, 1), join = Table.NestedJoin(j,{"i"},j,{"j"},"range"), replace = Table.ReplaceValue(join,0,each [Salary],(a,b,c)=> c  (try a[Salary]{0} otherwise 0),{"range"}), tax = Table.AddColumn(replace, "tax", each [range]*[Tax rate])][tax], (a,b,c)=>List.Sum(c[tax]), {"tax"}) in tax
But keep in mind: if, for example, Salary Amount = 15000, then for last 3k (15000  12000) tax will be equal zero, since there are no criterias for salary > 12000. So, if this scenario is unwanted, you should indicate knowingly large number for last criteria (for instance, 12 000 000 instead of 12 000).
 Edited by Aleksei Zhigulin Sunday, August 25, 2019 5:32 PM
 Marked as answer by Haider Alee Tuesday, August 27, 2019 6:25 AM

I believe this article should help you:
https://www.powerquery.training/portfolio/vlookuptrueinpowerquery/
 Marked as answer by Haider Alee Wednesday, August 28, 2019 1:10 PM


Hi Haider,
Each row in your Criteria table means upper bound of salary with corresponding tax rate. So, as I mentioned before, you should just choose knowingly large number for last criteria. No need to complicate the algorithm.
 Edited by Aleksei Zhigulin Monday, August 26, 2019 2:37 PM

another approach, without using Table.Replace.Value that Aleksei likes so much (and so do I)
I also amended the Criteria table that I found confusing: the 12,000 limit is not useful at all. I replaced upper bound limit by lower bound limit
Benefit: it manages all salaries even above 12,000
let TaxRate_tbl = #table( type table[lower bound limit=Int64.Type, rate=Percentage.Type], { {0,0}, {3000,0.05}, {6000,0.075}, {9000,0.12} //,{12000,0.12} //no need, duplicate criteria } ), Salary_tbl = #table( type table[id=Int64.Type, salary=Currency.Type], { {1,11000}, {2,2000}, {3,6000}, {4,20000} } ), TaxRate2_tbl = let Index.0 = Table.AddIndexColumn(TaxRate_tbl, "index.0",0,1), Index.1 = Table.AddIndexColumn(Index.0, "index.1", 1, 1), MergeIndex = Table.NestedJoin(Index.1, {"index.1"}, Index.1, {"index.0"}, "TOTO", JoinKind.LeftOuter), ExpandIndex = Table.ExpandTableColumn(MergeIndex, "TOTO", {"lower bound limit"}, {"upper bound limit"}) in ExpandIndex [[lower bound limit],[upper bound limit],[rate]], AddTax = Table.AddColumn(Salary_tbl, "tax", (S)=> List.Sum( Table.AddColumn(TaxRate2_tbl, "tax for each range", (T)=> T[rate] * ( if S[salary]<T[lower bound limit] then 0 else if T[upper bound limit]=null or S[salary]<T[upper bound limit] then S[salary]  T[lower bound limit] else T[upper bound limit]  T[lower bound limit] ) ) [tax for each range] ), Currency.Type ) in AddTax
 Edited by anthony34 Tuesday, August 27, 2019 9:12 AM
 Marked as answer by Haider Alee Tuesday, August 27, 2019 4:21 PM

Hi Aleksei,
Your query is absolutely ok and I am impressed with your use "Table.ReplaceValue" !!
But from my point of view performance is too low.
It is ok if we have 100 200 rows but try it, for example, for 500+ rows.
In that situation I think we should change approach to the problem.Below is my proposal.
// TaxTbl // This is similar to Your Criteria table let Source = Excel.CurrentWorkbook(){[Name="TaxTbl"]}[Content], TaxTbl = Table.Buffer( Table.FromColumns( { List.RemoveLastN(Source[Salary],1), List.Skip(Source[Tax rate], 1) }, Table.ColumnNames(Source) ) ), LG = {[Salary = 0, #"Tax rate" = 0, Sum = 0]} & List.Generate(() => [i = 0, S = 0, rec = TaxTbl{i} & [Sum = S]], each [i] < Table.RowCount(TaxTbl), each [ i=[i]+1, S = [S]+ (TaxTbl{i}[Salary]  TaxTbl{[i]}[Salary]) * TaxTbl{[i]}[Tax rate], rec = TaxTbl{i} & [Sum = S] ], each [rec] ), Buffered = Table.Buffer(Table.FromRecords(LG)) in Buffered // SalaryTbl // similar to your Taxes table let Source = Excel.CurrentWorkbook(){[Name="SalaryTbl"]}[Content], #"Added Custom" = Table.AddColumn( Source, "Tax", (x) => Table.Last( Table.SelectRows( TaxTbl, each [Salary]<x[Salary Amount] ) ) & [S = x[Salary Amount]] ), #"Transform Column" = Table.TransformColumns(#"Added Custom", {{"Tax", each [Sum] + ([S]  [Salary])*[Tax rate]}}) in #"Transform Column"
 Proposed as answer by Aleksei Zhigulin Wednesday, August 28, 2019 1:36 PM

Hi Bill
Actually I am not sure the speed issue in Aleksei's code comes only from the Table.ReplaceValue:
He is merging the indexes inside the Each, namely he is unecessarily doing the join for each rows of salary. I guess it is the source of the performance issue.
Of course, it could be moved out of the each (as I did) and then the speed should be better.Still I agree with you that Table.Replace.Value is not mandatory here.
In addition, your use of Table.FromColumns is defitively faster (and nicer) than the indexes join. But here the criteria table is very small and I am assuming the gain would be marginal.
 Edited by anthony34 Tuesday, August 27, 2019 10:11 AM


Hi Bill,
Thanks for your response. I totally agree with you  the performance is too far from ideal. No wonder, your very nice List.Generate solution is much faster. I've noticed minor bug, while testing your code: if Salary = 0, an error is returned.

