Measure for sum when match between two tables

[mahra-in]

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%

Please help

[BeidouZh]

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.

[BeidouZh]

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.

[Ashish_Mathur]

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.

[mahra-in]

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.

[Ashish_Mathur]

Hi,

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

[ccakjcrx]

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

Click HERE to access my .pbix file. 

I hope this helps! Have a good weekend.

[mahra-in]

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.

[Ashish_Mathur]

Hi @mahra-in,

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