Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mahra-in
Helper II
Helper II

Measure for sum when match between two tables

I have two table with related columns and perform action

 

Table 1 (Spend):

 

CategorySupplierSpend
MotorABB5000
DriveABB6000
DriveSiemens8000
CableABB9000
MotorGE1500
MotorSiemens3500
DriveSiemens3000
CableGE4500
DriveSiemens2500



Table 2 (Preferred Supplier):

CategorySupplier
MotorABB
CableGE
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

 

 

 

 

 

 

 

 

 

 

 

 

 

8 REPLIES 8
BeidouZh
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.

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

Ashish_Mathur
Super User
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.

 

Untitled.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
ccakjcrx
Resolver I
Resolver I

Hey @mahra-in!

 

Here is a screenshot of my solution:

 

Screenshot.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

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.

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.

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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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