Merging 2 queries and multiplying by a value

[aar0n]

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:

• all counts of "ID 666" and "ID 888" - but  - 
• Only applies to one count of "ID 333" (the one value left over in opportunity A for "ID 333" should use the "Opportunity A TC")  , and applies to two counts of "ID 444" (the one value left over in opportunity A for "ID 444" should use the "Opportunity A TC") 

	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

[v-shex-msft]

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.

all the secrets of summarize

Regards,
Xiaoxin Sheng

[aar0n]

@v-shex-msftthe month number is just a way of normalizing time. for example, month 12 is 1 year, month 24 is 2 years. 

[v-shex-msft]

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

[aar0n]

@Ashish_Mathur

@Phil_Seamark

@v-caliao-msft

@Greg_Deckler

@v-shex-msft

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.

[aar0n]

P.S. the Blue Bolded rows are just the Month Number.. for some reason, month # 17 refuses to show up on the forum..