Hi I need your help for the below I have two tables with repeated values as below Table 1: "Spend" (Excel Data) SupplierCategorySpendWegMotor5000ABBDrive4000SiemensCables1000TECOMotor5500SiemensCables1500TMEICCables1200ABBMotor4500WegDrive3500SiemensDrive3200GEDrive3000ABBMotor4000GECables1100TMEICMotor6000 43500 Table 2: "Preferred Supplier" (Sharepoint List - online) SupplierCategoryWegMotorWegDriveABBMotorSiemensMotorSiemensCablesGEDriveTMEICCables Since the above two tables have repeated values in both Supplier & Category I creatd a Bridge Table as below Table 3: Category Bridge CategoryMotorDriveCables And manage relationship with Table 1 & Table 2 Now what I need is When there is a match of Supplier & Catgeory from Table 2 with Table 1, add that Spend and finally divide the total with grand total For Eg: Weg preferred for Motor & Drive and the related spend is 5000 & 3500 respectivelySimilaly ABB for Motor alone and the related spend in Table 1 is 4500 + 4000 Overall the sum of spend when there is a match is 23700 and the total spend is 43500 Now 23700/43500 = 54% is the reslut I need RegardsMahes r

mahra-in

Helper II

8 years ago

Solved

Measure by comparing two columns in two different table

I need your help for the below

I have two tables with repeated values as below

Table 1: "Spend" (Excel Data)

Supplier	Category	Spend
Weg	Motor	5000
ABB	Drive	4000
Siemens	Cables	1000
TECO	Motor	5500
Siemens	Cables	1500
TMEIC	Cables	1200
ABB	Motor	4500
Weg	Drive	3500
Siemens	Drive	3200
GE	Drive	3000
ABB	Motor	4000
GE	Cables	1100
TMEIC	Motor	6000
		43500

Table 2: "Preferred Supplier" (Sharepoint List - online)

Supplier	Category
Weg	Motor
Weg	Drive
ABB	Motor
Siemens	Motor
Siemens	Cables
GE	Drive
TMEIC	Cables

Since the above two tables have repeated values in both Supplier & Category I creatd a Bridge Table as below

Table 3: Category Bridge

Category

Motor

Drive

Cables

And manage relationship with Table 1 & Table 2

Now what I need is When there is a match of Supplier & Catgeory from Table 2 with Table 1, add that Spend and finally divide the total with grand total

For Eg: Weg preferred for Motor & Drive and the related spend is 5000 & 3500 respectively

Similaly ABB for Motor alone and the related spend in Table 1 is 4500 + 4000

Overall the sum of spend when there is a match is 23700 and the total spend is 43500

Now 23700/43500 = 54% is the reslut I need

Regards

Mahes r

Need Help

Anonymous

8 years ago

This solution only requires Tables 1 and 2. No bridge table, no relationships.

Add this calculated column to Table 1 - Spend:

Preferred =
VAR Test =
    COUNTROWS (
        FILTER (
            PreferredSupplier,
            PreferredSupplier[Supplier] = Spend[Supplier]
                && PreferredSupplier[Category] = Spend[Category]
        )
    )
RETURN
    IF ( Test = 1, "Yes", "No" )

The Test variable checks to see if there is a match against the Preferred Supplier table, matching both on Supplier and Category. If there is, 1 row will be returned. Then the result in the column will be "Yes".

Then you can create the following measures:

[Total Spend] =
SUM ( Spend[Spend] )

[Approved Spend] =
CALCULATE ( [Total Spend], Spend[Preferred] = "Yes" )

[% Approved Spend] =
DIVIDE ( [Approved Spend], [Total Spend] )

mahra-in
8 years ago
Hi ChrisHaas

Your solution is working fine with small correction but I dont know why

Preferred =
VAR Test =
    COUNTROWS (
        FILTER (
            PreferredSupplier,
            PreferredSupplier[Supplier] = Spend[Supplier]
                && PreferredSupplier[Category] = Spend[Category]
        )
    )
RETURN
    IF ( Test = 1, "Yes", "No" )

In the above calculated column I chnaged the If statement as

IF ( Test = 0, "No", "Yes" )

remining everything I keep as you mentioned and working perfect.

But I said I still dont know what and why it gave wrong value first and right value once I changed the if statement

Thanks for the solution, it works now

4 Replies

Anonymous
Not applicable
8 years ago
This solution only requires Tables 1 and 2. No bridge table, no relationships.

Add this calculated column to Table 1 - Spend:

Preferred = VAR Test = COUNTROWS ( FILTER ( PreferredSupplier, PreferredSupplier[Supplier] = Spend[Supplier] && PreferredSupplier[Category] = Spend[Category] ) ) RETURN IF ( Test = 1, "Yes", "No" )

The Test variable checks to see if there is a match against the Preferred Supplier table, matching both on Supplier and Category. If there is, 1 row will be returned. Then the result in the column will be "Yes".

Then you can create the following measures:

[Total Spend] = SUM ( Spend[Spend] )

[Approved Spend] = CALCULATE ( [Total Spend], Spend[Preferred] = "Yes" )
[% Approved Spend] = DIVIDE ( [Approved Spend], [Total Spend] )
- mahra-in
  Helper II
  8 years ago
  Hi ChrisHaas
  
  Your solution is working fine with small correction but I dont know why
  
  Preferred =
  VAR Test =
      COUNTROWS (
          FILTER (
              PreferredSupplier,
              PreferredSupplier[Supplier] = Spend[Supplier]
                  && PreferredSupplier[Category] = Spend[Category]
          )
      )
  RETURN
      IF ( Test = 1, "Yes", "No" )
  
  In the above calculated column I chnaged the If statement as
  
  IF ( Test = 0, "No", "Yes" )
  
  remining everything I keep as you mentioned and working perfect.
  
  But I said I still dont know what and why it gave wrong value first and right value once I changed the if statement
  
  Thanks for the solution, it works now
- Mai_Nashaat
  Helper II
  5 years ago
  In this step
  RETURN IF ( Test = 1, "Yes", "No" )
  what if I want to return value from table preferred supplier, what can I do?
anandraman
Helper I
8 years ago
Check this PBIX

https://drive.google.com/open?id=1HK9jCBCp_1SLm1EDdoRWcM60UzlNlBE_

Forum Discussion

Measure by comparing two columns in two different table

4 Replies

Related content

KPI % Comparing Suppliers - Same measure shows different values

Compare different tables

Cumulative Difference from Another measure

Compare actual and target for measures across different tables

Compare different statuses measure