Progression calculation

[Anonymous]

Hello everyone!

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

Contribution Salary	Rate
Until R$ 1.045,00	7,5%
From R$ 1.045,01 to R$ 2.089,60	9%
From R$ 2.089,61 to R$ 3.134,40	12%
From R$ 3.134,41 to R$ 6.101,06	14%

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

[Greg_Deckler · Accepted solution]

@Anonymous - You basically need to implement a WHILE loop in DAX, here is how: https://community.powerbi.com/t5/Quick-Measures-Gallery/While-Loop/m-p/637535#M320

[v-shex-msft · Accepted solution]

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

[Ashish_Mathur · Accepted solution]

Hi,

You may refer to my solution - Compute transaction fee based on a tiered pricing model.

Hope this helps.

[v-shex-msft · Accepted solution]

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

[Asac_14]

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

[Greg_Deckler · Accepted solution]

@Anonymous - You basically need to implement a WHILE loop in DAX, here is how: https://community.powerbi.com/t5/Quick-Measures-Gallery/While-Loop/m-p/637535#M320