powerbi - DAX 公式计算奖金或所得税

Question

我正在尝试制定一项计算例如所得税的措施。起点始终是赚到的钱，我们称起点为 NetProfit。例如，我们以 10 万美元的净利润为例

Taxrate first 60k NetProfit is 40%
Taxrate remaining NetProfit is 70%
The outcome of the measure is amount of Tax
60k x 0.4 = 24k 
40k x 0.7 = 28k 
Total Tax = 52k (24k + 28k)

标签： powerbidaxmeasure

Answer 1

如果您有如下事实表

| Profit |
|--------|
| 40000  |
| 60000  |
| 100000 |
| 10000  |
| 61000  |

您可以通过以下措施实现总税

total:= SUM('fact'[Profit])

// if you want the subtotal tax with 60:40
totalTax:= 
VAR _1stTaxSlot = 60000
VAR _totalTax =
    IF (
        [total] <= _1stTaxSlot,
        [total] * 0.6,
        ( [total] - _1stTaxSlot ) * 0.4 + _1stTaxSlot * 0.6
    )
RETURN
    _totalTax

//if you want the subtotal level with summation of all calculated tax
totalTax2 :=
VAR _1stTaxSlot = 60000
VAR _totalTax2 =
    SUMX (
        'fact',
        IF (
            'fact'[Profit] <= _1stTaxSlot,
            'fact'[Profit] * 0.6,
            ( ( 'fact'[Profit] - _1stTaxSlot ) * 0.4 ) + ( _1stTaxSlot * 0.6 )
        )
    )
RETURN
    _totalTax2