cancel
Showing results for
Did you mean:
Frequent Visitor

## variable calculation based on total sum of metric

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 ? ?
1 ACCEPTED SOLUTION
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
2 REPLIES 2
Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!
Frequent Visitor

Announcements

#### Join Arun Ulag at MPPC23

Get a sneak peek into this year's Power Platform Conference Keynote.

#### Power BI September 2023 Update

Take a look at the September 2023 Power BI update to learn more.

#### Learn Live: Event Series

Join Microsoft Reactor and learn from developers.

#### Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Top Solution Authors
Top Kudoed Authors