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 by comparing two columns in two different table

Hi

 

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

2 ACCEPTED SOLUTIONS
Anonymous
Not applicable

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

View solution in original post

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

View solution in original post

4 REPLIES 4
anandraman
Helper I
Helper I

Anonymous
Not applicable

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

In this step 

RETURN
    IF ( Test = 1, "Yes", "No" )

what if I  want to return value from table preferred supplier, what can I do?

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

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.