Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Progression calculation

Hello everyone!

 

The Brazilian government has adopted a progressive table for the collection of social security contributions. The rates are as follows.

 

Contribution SalaryRate
Until R$ 1.045,007,5%
From R$ 1.045,01 to R$ 2.089,609%
From R$ 2.089,61 to R$ 3.134,4012%
From R$ 3.134,41 to R$ 6.101,0614%

 

Between each installment, the calculation is made considering the maximum and minimum of these and the rate to be applied. While the salary amount is not reached, it is considered the salary cap.

Here's how is the calculation for a salary of R$ 3,000.00 as an example, which is in the third track:

- 1st salary range: 1,045.00 x 0.075 = 78.38
- 2nd salary range: [2,089.60 - 1,045.00] x 0.09 = 1,044.60 x 0.09 = 94.01
- Salary range: [3,000.00 - 2,089.60] x 0.12 = 910.40 x 0.12 = 109.25
- Total to be collected: 109.25 + 94.01 + 78.38 = 281.64

With this result it is possible to calculate the effective rate that is about 9.39% (281.64 ÷ 3,000.00).

 

So, i would like to create a measure capable of doing this calculation. Can someone help me please?

 

Thank you!

 

Luis

Need Help
Tutorial Requests

4 Replies

  • Asac_14's avatar
    Asac_14
    Resolver I
    6 years ago

    Hi Anonymous  Here is the measure to compute effective rate - 

    Effective Rate =
    Var Sal=SUM('Salary'[Salary])
    Var Total=IF(Sal>3134.41,(1045*0.075)+(1044.60*0.09)+(1044.79*0.12)+(Sal-3134.41)*0.14,IF(Sal>2089.61,(1045*0.075)+(1044.60*0.09)+(Sal-2089.61)*0.12,IF(Sal>1045.01,(1045*0.075)+(Sal-1045.01)*0.09,(Sal-1045)*0.75)))
    Return
    DIVIDE(Total,Sal)
     

    Screenshot

    Replace with your table and field name. Pls accept it as solution if it solves your problem. Kudos are welcome as well 😉

     

    Thanks & Regards,

    KK

    +91-9940-28-4669

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI Anonymous,

    Please split your contribution salary to two fields based on 'to' character and remove others not used text strings, then you can write a measure formula to calculate on them.

    Measure = 
VAR summary =
    SUMMARIZE (
        'Table',
        [Contribution Salary From],
        [Contribution Salary To],
        [Rate],
        "Result", ( [Contribution Salary To] - [Contribution Salary From] ) * [rate]
    )
RETURN
    SUMX ( summary, [Result] )

    Regards,

    Xiaoxin Sheng