variable calculation based on total sum of metric

[dpancratz]

Hi, I'm somewhat of a novice and trying to calcuate a margin that varies based on the sum. 

For example

• The margin is 30% up to $12,000
• When revenues exceed $12,000, the margin is 30% on the first $12,000 and then 20% on everything after $12K

Is there a way to create an active calculation for this in Power BI? 

I'd like to calculate and show the margin amount over time. In the example data below, I have week-by-week data that goes back to before the 12K threshold and upcoming weeks that will exceed the 12K threshold. How can I create an active calculation so that I can visualize the week-by-week margin despite the variable rate? 

Week	Gross Revenue	Margin Amount
1/6/2017	$        10,500.00	$               3,150.00
1/13/2017	$        11,000.00	$               3,300.00
1/20/2017	$        11,750.00	$               3,525.00
1/27/2017	$        12,500.00	$               3,700.00
2/3/2017	$        13,250.00	$               3,850.00
2/10/2017	$        14,500.00	$               4,100.00
2/17/2017	$        15,250.00	$               4,250.00
2/24/2017	$        16,000.00	$               4,400.00
3/2/2017	?	?

[CheenuSing · Accepted solution]

hi @dpancratz

Try creating a calculated column as follows

MarginAmount = IF( [Gross Revenue] <= 12000 ,

                                                                   [Gross Revenue] *.30,
                                                                  3600 + ([Gross Revenue] - 12000 ) * .20 )

The output generated based on your data using the above formula 

If this solves your issue, please acceept this as a solution and also give KUDOS.

Cheers

CheenuSing

[dpancratz]

Very helpful! Thank you!