Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dpancratz
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? 

 

WeekGross RevenueMargin 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
CheenuSing
Community Champion
Community Champion

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 

Averages.GIF

 

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!

View solution in original post

2 REPLIES 2
CheenuSing
Community Champion
Community Champion

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 

Averages.GIF

 

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!

Very helpful! Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.