Analysing Revenue vs Opportunity Count Over Time in Microsoft Power BI
Table of Contents
Introduction
Using Microsoft Power BI, it is possible to compare Revenue (actual income) with Opportunity Count (number of sales opportunities) using a Line and Clustered Column Chart.
This type of visualization will enable you to understand whether an increasing number of opportunities is translating to higher revenues monthly. Additionally, it is possible to drill down from a yearly view to a monthly view for a more detailed analysis. In this blog, you will learn how to create a progress analysis report.
For more information on how to improve your Power BI reports using AI, you can also check out this beginner’s guide:
https://powerbitraining.com.au/level-up-your-power-bi-reports-with-chatgpt-a-beginners-guide/
By the end of this tutorial, you will have an interactive report that compares Revenue and Opportunity Count over time.
Why Progress Analysis is Important
Analysing progress over time helps organizations:
- Compare actual revenue vs targets.
- Identify performance gaps.
- Monitor trends month-over-month.
- Make data-driven adjustments.
- Improve forecasting accuracy.
This type of analysis is commonly used in sales and finance dashboards.
Step-by-Step: Create Progress Analysis Visual
For effective decision-making, it is important to analyse the progress you have made over time.
Using the Microsoft sample reports file, open Revenue Opportunities.pbix. You can also download the report directly from this blog.
Create a visual that helps you analyse and track your progress.
- Click on the Line and clustered column chart Visual in the Visualizations Pane.
- Drag and drop Date from Date_Table to X-axis Value.
- From the Date hierarchy, remove all fields other than Month and Year.
- Drag and drop the column Opportunity Count from the Fact Table to the Line y-axis.
- Drag and drop the column Revenue from Fact Table to the Column y-axis.
- Now click on Turn on drill down option and then click on Expand all down one level in the hierarchy drill down option at the top of the Line and clustered column chart Visual.
- Select Fatum in the Product Code Slicer.
How to Interpret the Chart
- The columns represent Revenue.
- The line represents Opportunity Count.
- If revenue increases as opportunity count increases, it indicates strong conversion performance.
- If opportunity count increases but revenue does not increase proportionally, it may indicate lower deal values or poor conversion.
- If revenue increases while opportunity count remains stable, it may indicate higher-value deals.
This comparison helps analyse sales efficiency and performance trends.
Best Practices
- Use a proper Date table and mark it as a Date Table.
- Maintain correct relationships between Sales and Goal tables.
- Keep consistent monthly granularity for revenue and targets.
- Use slicers for dynamic filtering (Product, Region, Year).
- Add tooltips for additional details if needed.
Business Use Cases
This Revenue vs Target comparison is commonly used in:
- Sales performance dashboards.
- Financial review reports.
- Budget vs Actual analysis.
- Monthly management reporting.
- Product performance tracking.
Common Mistakes to Avoid
- Using auto date hierarchy without cleaning it.
- Not enabling drill-down feature.
- Incorrect relationship between Goal and Sales tables.
- Mixing granularity (daily sales with monthly targets).
- Not filtering product categories properly.
Avoiding these mistakes ensures accurate results.
Conclusion
The creation of a Revenue vs Opportunity Count report in Power BI enables organizations to analyse how sales opportunities translate into revenue over time. By comparing Revenue and Opportunity Count in a Line and Clustered Column Chart, businesses can identify trends, evaluate sales effectiveness, and make informed decisions.
FAQs
Use slicers like Product Line or Region to filter dynamically.
Yes. You can add YTD measures using DAX functions.
Extended Learning & References
For more in-depth learning and advanced Power BI training, enrol in
Downloads
