Forum Discussion

myou's avatar
myou
Icon for Helper II rankHelper II
9 years ago

How to generate a pipeline based on multiple columns

I am new to Power BI and DAX . I really dont know from where to start.

I have as input

1) Target value of an account manager

2) Target date of account manager

3) Account manager ID

4) AVG probability to move from one stage to other is 50 percent

5) AVG time to move from one stage to other is 1 month(30days)

 

Lets says Person x have a target of 500000 in 1-12-2016

                                                        250000 in 1-9-2016

                                                        250000 in 1-6-2016

And we took the information from a certain column that its 50 percent to move from one stage to other and the time to move from one stage to other is one month.

The blue columns are genrated  because the time to move is one month

The red text is our given targets

What I want to generate are the numerical vaues other than the red

 

 

Result:

 JanFebMarAprMayJunJulAugSepOctNovDec
Prospect  16000000  32000000      
Qualify8000000  8000000  16000000     
Develop 4000000  4000000  8000000    
Solution  2000000  2000000  4000000   
Proof   1000000  1000000  2000000  
Close    500000  500000  1000000 
Won     250000  250000  500000

 

Data Structure: TargetsTable

AM IDDateValue
11/12/2016500000
11/9/2016250000
11/6/2016250000
21/12/2016500000
21/9/2016250000
21/6/2016250000

 

Table2: that has information about the probabilities for a certain account manager. For simplicity all stages move with 50 percent. For example Average probability from stage 1 to stage 2 is 50 percent and the AVG time from stage 1 to stage 2 is one month

AMIDAVGprobability1to2AVGprobability2to3AVGprobability3to4AVGprobability4to5AVGprobability5to6AVGprobability6to7AvgTime1to2AvgTime2to3AvgTime3to4AvgTime4to5AvgTime5to6AvgTime6to7
1505050505050303030303030
2505050505050303030303030

 

 

Need Help
Show and Tell

2 Replies

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    9 years ago

    Hi myou,

    Based on my understanding, the TargetsTable is your resource table, you want to get the Result table, right? If it is, where do the values(Prospect,Qualify,Develop,Solution,Proof,Close,Won) come from? In Power BI desktop, you must have a table which including these values. Please post more details for further analysis.

    Thanks,
    Angelia

    • myou's avatar
      myou
      Icon for Helper II rankHelper II
      9 years ago

      Hi v-huizhn-msft

      Resource tables are the targets table and other table which has the average probabilities and time. And the results table for one account manager id. Am not sure if it can be done. Stages can be taken from a seperate table I create that has 

      stages

      prospect

      qualify

      develop

      solution

      proof

      close

      won