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.
Hi Guys,
I have a source table that looks like the following: please note that B is dependant on A. There always has to be more "Opportunity A's" than there is "Opportunity B's"
ID | Count of Opportunity A | Count of Opportunity B | Count of Opportunity C | Count of Opportunity D | Chance of success (%) |
111 | 0 | 0 | 5 | 0 | 50 |
222 | 1 | 0 | 0 | 0 | 20 |
333 | 2 | 1 | 1 | 0 | 10 |
444 | 3 | 2 | 0 | 0 | 25 |
555 | 0 | 0 | 1 | 0 | 75 |
666 | 2 | 2 | 0 | 1 | 60 |
777 | 0 | 0 | 0 | 0 | 10 |
888 | 1 | 1 | 1 | 1 | 15 |
i need the table that is above, to be combined with a "type curve" table as shown below. the table below shows what 1 count of each opportunity looks like. the orange row that says "2*Opportunity A" is just my way of visualizing what happens when the "count of opportunity" in the source table above is more than 1.
opportunity A&B applies to "ID's" where there an opportunity B exists.. so for example, it applies to:
1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | ||
Opportunity A TC | 16 | 20 | 4 | 13 | 3 | 10 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Opportunity A&B TC | 16 | 20 | 4 | 13 | 3 | 10 | 6 | 25 | 14 | 23 | 13 | 23 | 13 | 21 | 25 | 22 | 10 |
2* Opportunity A TC | 32 | 40 | 8 | 26 | 6 | 20 | 12 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Opportunity B TC | 25 | 14 | 23 | 13 | 23 | 13 | 21 | 25 | 22 | 10 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Opportunity C TC | 39 | 36 | 42 | 42 | 36 | 38 | 28 | 41 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Opportunity D TC | 32 | 18 | 22 | 17 | 24 | 32 | 24 | 16 | 18 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
what i'm hoping to get is a final calculated table that holds the type curve that's multiplied by the number of times the count appears in the source table, as well as multiplied by each Chance of Success - for each ID
I will give an example of what the solution should look like for a couple broken up examples.. the final table only needs to show the "final result" row.. i'm just breaking it down to better explain what i'm looking for.
for "ID 111"
Italics and Orange means that it is only being shown for demonstration purposes | ||||||||||
ID | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | |
First step in calculation for the 5 "C" opportunities in this ID | 111 | 195 | 180 | 210 | 210 | 180 | 190 | 140 | 205 | 0 |
Final Result ( the value shown in the row above, multiplied by the "Chance of Success for that particular ID - in this case, it is 50% or 0.5) | 111 | 97.5 | 90 | 105 | 105 | 90 | 95 | 70 | 102.5 | 0 |
for "ID 333"
Italics and Orange means that it is only being shown for demonstration purposes | ||||||||||||||||||
ID | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 | 10 | 11 | 12 | 13 | 14 | 15 | 16 | ||
Broken Down Calculation for the 1 "A" opportunity in this ID | 333 | 16 | 20 | 4 | 13 | 3 | 10 | 6 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Broken Down Calculation for 1 "A & B" opportunity in this ID | 333 | 16 | 20 | 4 | 13 | 3 | 10 | 6 | 25 | 14 | 23 | 13 | 23 | 13 | 21 | 25 | 22 | 10 |
Broken Down Calculation for the 1 "C" opportunity in this ID | 333 | 39 | 36 | 42 | 42 | 36 | 38 | 28 | 41 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
Final Result (sum of all the opportunities for that particular ID, multiplied by the Chance of Success for that Particular ID - in this case, 10%, or 0.1) | 333 | 7.1 | 7.6 | 5 | 6.8 | 4.2 | 5.8 | 4 | 6.6 | 1.4 | 2.3 | 1.3 | 2.3 | 1.3 | 2.1 | 2.5 | 2.2 | 1
|
So far, the only way i've been able to do it is with a variety of merges and table transformations..
i've edited the "Type Curve" table so that it includes a type curve for every variation.. but it's a broken process.. if anything gets updated in the source table, and the type curve and count of opportunities variation wasn't created, it won't work..
then after the type curve table is created, i merge it with the source table, using the "counts" as a common column.
after this table is merged, i was planning on creating a calculated column for each 'month' and multiply it with the "COS" for that particular ID
the issue is that i'm already at too many steps for the report to work efficiently(there's about 7000 rows of data.. i can't post my workbook because it is confidential data) .. and i am at a loss of how to program this in DAX, Power Query, or R-Script.
Thank you guys for the help,
Aaron
HI @aar0n,
First of all, it will be help if you share some detail information about your requirement:
How to Get Your Question Answered Quickly
As you mentioned, 'bold blue' part is month number, I thinks like a customize calendar table, you need show us some sample about this calendar.(I don't think normal calendar contains month number which great than 12)
In addition, there are lots of custom row labels and data categories in example table, I don't think you can direct create them in visual.(current measure not support as group item in matrix visual)
In my opinion, you need to use unpivot column function to transform your source table first to expand detail records with opportunity.
Unpivot Data Using Excel Power Query
For custom data categories, you can try to use summarize function to summary records with if statements/conditions.
Regards,
Xiaoxin Sheng
@v-shex-msftthe month number is just a way of normalizing time. for example, month 12 is 1 year, month 24 is 2 years.
HI @aar0n,
I'm not so sure why you need to expand next year months. For these additional months, you need to add some convert data steps.
In addition, I still not so clarify for your grouping data operation, can you please share some sample data and expected result?
Regards,
Xiaoxin Sheng
Hey guys, this thread has been silent for over a week, and i was just wondering if anyone has any solutions in the works..
thanks for the help guys.
P.S. the Blue Bolded rows are just the Month Number.. for some reason, month # 17 refuses to show up on the forum..
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 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |