cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### 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!

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors