none
How to calculate tax amount dynamically. RRS feed

  • 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

    Saturday, August 24, 2019 7:53 AM

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

    Sunday, August 25, 2019 5:19 PM
    • Marked as answer by Haider Alee Wednesday, August 28, 2019 1:10 PM
    Sunday, August 25, 2019 8:00 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
    Tuesday, August 27, 2019 8:58 AM

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

    Sunday, August 25, 2019 5:19 PM
    • Marked as answer by Haider Alee Wednesday, August 28, 2019 1:10 PM
    Sunday, August 25, 2019 8:00 PM
  • Many thanks for replying.

    In that case amount exceeding 12000 will be calculated at 12% . In short, if salary is 15000 then 3k will be calculated at 12% and remaining amount will also be calculated @12% as well.  

     

    Hayder Alee

    Monday, August 26, 2019 7:24 AM
  • 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.

    Monday, August 26, 2019 2:36 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
    Tuesday, August 27, 2019 8:58 AM
  • 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"

    Tuesday, August 27, 2019 9:27 AM
  • 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
    Tuesday, August 27, 2019 10:06 AM
  • Hi Anthony,

    Many thanks for sharing code that works even if salary is above 12k

    May you please share workbook with solution on haider.ali7333@gmail.com


    Hayder Alee

    Tuesday, August 27, 2019 4:26 PM
  • 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.

    Wednesday, August 28, 2019 2:52 PM
  • Hi Anthony :-)

    I didn't analyze Aleksei's code for bottlenecks. Just observed that my is much faster. I just noticed that it's not very fast, so I wrote the code by myself. But thanks for your analysis :-)

    Thursday, August 29, 2019 9:01 AM
  • Hi Aleksei,

    This error can be eliminated by changing step #"Added Custom" in "SalaryTbl" query from "<" to "<="... that's all :-)). Thanks for your kind words :-)

    Thursday, August 29, 2019 9:06 AM