Displaying Estimated Pipeline Targets

DAX DAX Calculations
1

Management is asking the sales team to maintain a sales pipeline based on goals for future months.

  • Sales Development Target = Sales goal in 3 months X 3
  • Qualification Target = Sales goal in 2 months X 2
  • Proposal Target = Sales goal in 1 month X 1.5
  • Customer Approval Target = Sales goal this month X 0.8

I’ve created DAX formulas that calculate the current month pipeline target. How do I display this for upcoming months? Details are in the attached pbix.
Sales Goals.pbix (326.2 KB)

2

See if it helps
Sales Goals (1).pbix (326.7 KB)

image
image843×428 70.4 KB

3

Thanks @VilmarSchi. This formula works for the territories where there are opportunities in each of the sales stages. But not all territories currently have opportunities at each of the stages. How do I modify this so that it calculates all SalesStages?

4

Can you cite an example

5

There is a filter on the page you duplicated called Territory. If you select BB1, you’ll see that only two sales stages are represented in the results.

6

Disable the relationship between the Territory and Opps tables

image
image944×272 21.9 KB

7

This works, but it breaks all the other visuals that depend on the relationship. So for anyone else looking at this solution, when the relationship is set to not active, activate the relationship in the DAX formula with USERELATIONSHIP.

Goal equation:
Target =
SUMX (
VALUES ( Opps[SalesStage] ),
CALCULATE (
SWITCH (
TRUE (),
SELECTEDVALUE ( Opps[SalesStage] ) = “Sales Development”, CALCULATE ( [Total Goals] * 3, DATEADD ( Dates[Date], 3, MONTH ) ),
SELECTEDVALUE ( Opps[SalesStage] ) = “Qualification”, CALCULATE ( [Total Goals] * 2, DATEADD ( Dates[Date], 2, MONTH ) ),
SELECTEDVALUE ( Opps[SalesStage] ) = “Proposal”, CALCULATE ( [Total Goals] * 1.5, DATEADD ( Dates[Date], 1, MONTH ) ),
SELECTEDVALUE ( Opps[SalesStage] ) = “Customer Approval”, [Total Goals] * 0.8
)
)
)

Actual opportunities by territory:
Opportunity Value EstCloseDate =
CALCULATE(
SUMX(Opps,Opps[EstimatedValue]),
USERELATIONSHIP(Opps[TerritoryNbr],Territory[Territory Nbr]),
USERELATIONSHIP(Opps[EstimatedCloseDate],Dates[Date])
)

Thanks @VilmarSchi.