Matching one to many and aggregating

[Anonymous]

Hi All,

I'm running powerBI across two spreadsheets, it's the weekly CRM Opportunity ID extract.

Table 1 is "Latest" and Table 2 is "Previous".

I want to do a mapping and connection of "NewOps", (ops which were not in the particular extract last week but are now.  This could be either because they're brand-new ops, or else they're ops which were not expected to close in the selected time horizon but now are).

The challenge is that there is not really a unique matching because there will be multiple records for each unique "Opportunity ID".

e.g. Table 1 may have 3 entries for Acme Bank 

Opp12345 | Acme Bank | Total Value $120 | Solution Value $00

Opp12345 | Acme Bank | Total VAlue $0   | Solution 1 Value $40  | Field A

Opp12345 | Acme Bank | Total Value $0   | Solution 2 Value $55  | Field B

Opp12345 | Acme BAnk | Total VAlue $0   | Solution 3 Value $25  | Field A

Opp23456 | BCME Shop | Total VAlue $57  | Solution 1 Value $25  | Field A

I created a table called "NewOps" which uses.

NewOps = EXCEPT(VALUES(Latest[Opportunity ID]), VALUES(Previous[Opportunity ID]))

It has columns in it such as 

NewSolVal = CALCULATE(VALUES(Latest[SolVal]), FILTER(Latest,NewOps[Opportunity ID] = Latest[Opportunity ID]))

The Trouble is... 

The report works if I use "remove duplicates" in the query on OppID, and it will show the new Opps, but then it won't show the full SOLUTIONVALUE, because it will only pick up the first record.

If I take away "remove duplicates", then the visualisations fail

A table of multiple values was supplied where a single value was expected.

is there a better way of doing this ?   thanks!

[v-juanli-msft]

Hi @Anonymous

By creating a new table with DAX as the following

NewOps = EXCEPT(VALUES(Latest[Opportunity ID]), VALUES(Previous[Opportunity ID]))

i can get a table with a distinct column

Follow your ideas, then create an column

NewSolVal = CALCULATE(SUM(Latest[Solution Value]),ALLEXCEPT('Table','Table'[Opportunity ID]))

Before you need to create relationship between  tables belwo

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @Anonymous

1.It has columns in it such as 

NewSolVal = CALCULATE(VALUES(Latest[SolVal]), FILTER(Latest,NewOps[Opportunity ID] = Latest[Opportunity ID]))

error :"A table of multiple values was supplied where a single value was expected."

"VALUES" function returns a table instead of a column. so it runs with error message.

2.

"NewOps", (ops which were not in the particular extract last week but are now.  This could be either because they're brand-new ops, or else they're ops which were not expected to close in the selected time horizon but now are).

To get a new table including "NewOps", go to Edit Queries->Merge queries as new

Join kind:

Left Anti(rows only in first) 

Then remove the "Previous" column in the table above

Close&&apply

3. to get total solution  based on Opp or Field, you could create calculated columns

total solution = CALCULATE(SUM(Merge1[Solution Value]),ALLEXCEPT(Merge1,Merge1[Opportunity ID]))

total Field = CALCULATE(SUM(Merge1[Solution Value]),ALLEXCEPT(Merge1,Merge1[Field]))

Best Regards

Maggie

[Anonymous]

I meant to say that the total solution value doesn't always add up to the total value.  (e.g. there may be additional non-solution related value).

Also.. I would be looking to spit out two visualisations

Opp12345 - Total Solution value $120.  (not total value.. sum of individual solutions)

Opp23456 - Total Solution Value $87

Field A $90.  (made up of 40+25 from opp12345 plus $25 from opp23456)

Field B  $55 (made up of 55 from opp 12345 and nothing from opp23456 because there was no field B solution value for that solution in Opp23456)

thanks

Gavin

[CheenuSing]

Hi @Anonymous,

Can you put some sample data in excel for both Latest and Previous dataset in google drive or one drive and share the link. Even better would be the current pbix you are working on.

Cheers

CheenuSing