Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec | |
Prospect | 16000000 | 32000000 | ||||||||||
Qualify | 8000000 | 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 ID | Date | Value |
1 | 1/12/2016 | 500000 |
1 | 1/9/2016 | 250000 |
1 | 1/6/2016 | 250000 |
2 | 1/12/2016 | 500000 |
2 | 1/9/2016 | 250000 |
2 | 1/6/2016 | 250000 |
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
AMID | AVGprobability1to2 | AVGprobability2to3 | AVGprobability3to4 | AVGprobability4to5 | AVGprobability5to6 | AVGprobability6to7 | AvgTime1to2 | AvgTime2to3 | AvgTime3to4 | AvgTime4to5 | AvgTime5to6 | AvgTime6to7 |
1 | 50 | 50 | 50 | 50 | 50 | 50 | 30 | 30 | 30 | 30 | 30 | 30 |
2 | 50 | 50 | 50 | 50 | 50 | 50 | 30 | 30 | 30 | 30 | 30 | 30 |
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |