Need help to build a sales funnel report by sql query

I have created a view for sales.In this view, there are relations among lead, opportunity and quotation. We can see not every lead turns to opportunity and quotation.

LeadID OfferingID QuotationID Product Salesperson Department Date Salesprice

L1      O1       Q1         X001     Mr.X       Machine Sales 11-01-2011  100
L2      O2       Q2         X002     Mr.Y       Marine Sales  10-02-2011  200
L3      O3                  X003     Mr.Z       Engine Sales  11-03-2011  300
L4      O4       Q3         X004     Mr.P       Parts Sales   13-04-2011  50
L5                          X001     Mr.X       Machine Sales 20-05-2012  100
L6      O5                  X001     Mr.X       Machine Sales 30-06-2012  100

My final output for the sales funnel for all department will be like [total number of leads (6)]->[total number of offering(5)]->[total number of quotations(3)]. If i want to filter it by ‘Machine Sales’ department ,the funnel will be like: [total number of leads (3)]->[total number of offering(2)]->[total number of quotations(1)].. i need to be able to filter the funnel by date,salesperson,product and department.please help me to build this sales funnel query. i will then visualize the data in microsoft powerbi after implementing the query which will be in a funnel shape.

Answer

Is there anything stopping you from feeding this data directly into Power BI?

I think you might be over-engineering this problem, and creating another table/view on you database that you’ll have to remember/manage.

Leads = COUNT('YourTableNameHere'[LeadID])
Offers = COUNT('YourTableNameHere'[OfferID])
Quotes = COUNT('YourTableNameHere'[QuoteID])