cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Measure for sum when match between two tables

I have two table with related columns and perform action

Table 1 (Spend):

 Category Supplier Spend Motor ABB 5000 Drive ABB 6000 Drive Siemens 8000 Cable ABB 9000 Motor GE 1500 Motor Siemens 3500 Drive Siemens 3000 Cable GE 4500 Drive Siemens 2500

Table 2 (Preferred Supplier):

 Category Supplier Motor ABB Cable GE Drive Siemens

The total spend from table 1 is 43000

I need a measure to check the combination of category and supplier from table 2 and sum the spend only when the combination exists in table 1

Motor ABB - 5000

Cable GE - 4500

Drive Siemens - 13500

Sum = 23000

I want the reuslt as 23000/43000 = 53%

8 REPLIES 8
New Member

HI

My resolution:

1. add a customer column in sheet 2, named as your request, such as " perfect choice"... and filled with “yes”

2. merge sheet2 to sheet 1 when category and supplier are both matched in the two sheet. and get the "yes" column

3. add new quick measure-filted value:  calculate the speed column,by filte "yes" column. named "yes value".

4. add new quick measure-division:  get  (SUM Speed)/ (SUM yes value). it is 0.53.

sorry,I don't have resource to update the my screen picture. wish it helpful.

New Member

HI

My resolution:

1. add a customer column in sheet 2, named as your request, such as " perfect choice"... and filled with “yes”

2. merge sheet2 to sheet 1 when category and supplier are both matched in the two sheet. and get the "yes" column

3. add new quick measure-filted value:  calculate the speed column,by filte "yes" column. named "yes value".

4. add new quick measure-division:  get  (SUM Speed)/ (SUM yes value). it is 0.53.

sorry,I don't have resource to update the my screen picture. wish it helpful.

Super User

Hi,

Here's my attempt:

1. I created a relationship between the Category column of the Spend Table to the Category column of the preferred_supplier table.  I have assumed that in the Category column of the preferred_supplier table, there will be no duplicates
2. In the spend Table, i wrote the following calculated column formula

=if(not(ISBLANK(LOOKUPVALUE(preferred_supplier[Category],preferred_supplier[Category],Spend[Category],preferred_supplier[Supplier],Spend[Supplier]))),"Preferred","General")

1. In the visual, i dragged Category and Supplier from the preferred_supplier table
2. I wrote the following measure in the Spend table

=CALCULATE(SUM(Spend[Spend]),Spend[Status]="Preferred")/SUM(Spend[Spend])

Here's the file.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper II

Hi Ashish

The measure is not working since I made the mistake by giving incomplete info. The tables tables were not directly related and both tables have repeated category for eg: Motor is preferred for ABB , Siemens and so on. Hence I created a bridge table to related these table.

Super User

Hi,

In that case, please share a "well thought over dataset(s)" with your expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Resolver I

Hey @mahra-in!

Here is a screenshot of my solution:

Here is the measure I used:

```MsrSUMMatch =
CALCULATE(
SUM(Spend[Spend]),
FILTER(Spend,
Spend[Category] = RELATED(PreferredSupplier[Category])
&& Spend[Supplier] = RELATED(PreferredSupplier[Supplier])
)
)```

The percentage measure is as follows:

```MsrSUM% =
DIVIDE([MsrSUMMatch],SUM(Spend[Spend]))```

I hope this helps! Have a good weekend.

Helper II

Hi

Sorry I missed to mention the Table 1 & Table 2 are not directly related since both tables has category repeated and hence I had created a bridge table to relate these tables

Now when I apply the measure you sent me the related is not working

Can you help me on that.

Super User

Hi @mahra-in,

I do not know whom you are replying to but did you try my solution?

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors