Hi @PANNEER ,
Try below steps.
1. Create a Stage table (for sorting & consistency)
In Power BI Desktop:
Go to Modeling ➜ New table
Paste this DAX:
Stage =
DATATABLE (
"StageKey", INTEGER,
"StageName", STRING,
"SortOrder", INTEGER,
{
{ 1, "Prospecting", 1 },
{ 2, "Qualification", 2 },
{ 3, "Offer & Negotiation", 3 },
{ 4, "Booking", 4 },
{ 5, "Closing", 5
}
}
)
In Data view, select the Stage table.
Select StageName column → Column tools ➜ Sort by Column ➜ SortOrder
2. Map each record to a Stage (calculated column)
Now we need a column in your main table (call it Opportunities for example) that says which stage each row is in based on your rules:
Prospecting – Draft lead
Qualification – Qualified lead
Offer & Negotiation – Quotation sent
Booking – Reservation created from quotation
Closing – Agreement created
In Modeling ➜ New column on your main table:
StageName =
VAR HasAgreement = NOT ISBLANK ( Opportunities[AgreementDate] ) // or status/flag
VAR HasBooking = NOT ISBLANK ( Opportunities[ReservationDate] ) // or status/flag
VAR HasQuotation = NOT ISBLANK ( Opportunities[QuotationDate] ) // or status/flag
VAR IsQualified = Opportunities[LeadStatus] = "Qualified"
VAR IsDraft = Opportunities[LeadStatus] = "Draft"
RETURN
SWITCH (
TRUE (),
HasAgreement, "Closing",
HasBooking, "Booking",
HasQuotation, "Offer & Negotiation",
IsQualified, "Qualification",
IsDraft, "Prospecting",
BLANK() // or "Unknown"
)
3. Connect Stage table to your data
Go to Model view.
Drag from Stage[StageName] → Opportunities[StageName] to create a relationship
Cardinality: Many-to-one (Opportunities → Stage)
Cross filter: Single is fine.
Now your model knows which stage each opportunity belongs to, and you have a clean stage dimension for ordering.
4. Create a measure for funnel values
You usually want to count leads/opportunities per stage.
In Modeling ➜ New measure:
Leads Count =
DISTINCTCOUNT ( Opportunities[LeadID] ) -- or OpportunityID / CustomerID
5. Build the funnel chart
Go to Report view.
Insert a Funnel visual.
In the Fields pane:
Category → Stage[StageName]
Values → [Leads Count]
Make sure the funnel is sorted by Stage[SortOrder]:
Click the … on the visual → Sort by StageName
Then in the dropdown choose Sort by SortOrder (if available), or sort your StageName alphabetically to match the SortOrder if needed.
If my response as resolved your issue please mark it as solution and give kudos.
