Forum Discussion

aar0n's avatar
aar0n
Advocate II
8 years ago

Merging 2 queries and multiplying by a value

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"

IDCount of Opportunity ACount of Opportunity BCount of Opportunity CCount of Opportunity DChance of success (%)
111005050
222100020
333211010
444320025
555001075
666220160
777000010
888111115

 

 

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") 

 

  
 12345678910111213141516
Opportunity A TC162041331060000000000
Opportunity A&B TC1620413310625142313231321252210
2* Opportunity A TC3240826620120000000000
Opportunity B TC251423132313212522100000000
Opportunity C TC3936424236382841000000000
Opportunity D TC32182217243224161800000000

 

 

 

 

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  
 ID123456789

First step in calculation  for the 5 "C" opportunities in this ID

1111951802102101801901402050

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)

11197.590105105909570102.50

 

for "ID 333"

 Italics and Orange means that it is only being shown for demonstration purposes  
 ID12345678910111213141516
Broken Down Calculation for the 1 "A" opportunity in this ID333162041331060000000000
Broken Down Calculation for 1 "A & B" opportunity in this ID3331620413310625142313231321252210
Broken Down Calculation for the 1 "C" opportunity in this ID3333936424236382841000000000
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)3337.17.656.84.25.846.61.4

2.3

1.32.31.32.12.52.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

General Comment
Need Help
Tutorial Requests

5 Replies

  • aar0n's avatar
    aar0n
    Advocate II
    8 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      aar0n
      Advocate II
      8 years ago

      Anonymousthe month number is just a way of normalizing time. for example, month 12 is 1 year, month 24 is 2 years. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        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