Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
Hi, I'm somewhat of a novice and trying to calcuate a margin that varies based on the sum.
For example
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 | ? | ? |
Solved! Go to Solution.
hi @Anonymous
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
hi @Anonymous
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
Very helpful! Thank you!
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
56 | |
54 | |
54 | |
37 | |
29 |
User | Count |
---|---|
78 | |
64 | |
45 | |
40 | |
40 |