cancel
Showing results for
Did you mean:

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.

Anonymous
Not applicable

## Matching one to many and aggregating

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!

1 ACCEPTED SOLUTION
Community Support

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

4 REPLIES 4
Community Support

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

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

Before you need to create relationship between  tables belwo

Best Regards

Maggie

Community Support

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
Not applicable

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

Community Champion

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

Did I answer your question? Mark my post as a solution and also give KUDOS !

Proud to be a Datanaut!

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors