Calculated Measures based on conditions across tables

[Anonymous]

Hi!

This report will show a car shop how they can save of parts by switching suppliers and/or negotiating. 

I am needing to create the following calculations from the data below:

1) Cost Savings for Same Part, Same Supplier

2) Savings from Same Part Different Supplier

3)Saving based on Alternate Part, Same Supplier

4) Savings based on Alternate Part, Alternate Supplier

(Table A)

There is a shop that is looking at their data for a year, they can see what parts they bought, from which supplier, and how much each part costs. 

(Table B) 

This table shows how much that same part is being sold, on average across different suppliers.

(Table C)

This table shows alternate parts, the supplier, and its average price. 

A

PrimaryPartConcat	PartSupplier	AVGCostEach
1105065 NAPA CLUTCHES	Supplier 4	$125.53
52641404 VALEO	Supplier 1	$130.00
MU18901D PERFECTION CLUTCH	Supplier 5	$138.53

B

PrimaryPartConcat	PartSupplier	AVGCostEach
05065 LUK AUTOMOTIVE SYSTEMS	Supplier 1	$139.99
05065 LUK AUTOMOTIVE SYSTEMS	Supplier 3	$143.53

C

PrimaryPartConcat	PartSupplier	AVGCostEach
1105065 NAPA CLUTCHES	Supplier 4	$125.53
52641404 VALEO	Supplier 1	$130.00
MU18901D PERFECTION CLUTCH	Supplier 5	$138.53

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

I build three tables like yours to have a test.

TableA:

TableB:

TableC (I add an Alternate Part column in this table):

1.     Cost Savings for Same Part, Same Supplier (output the smaller avg in A/B with same part and same supplier with A)

1_Avg = 
VAR _MinAvg_B =
    CALCULATE (
        MIN ( B[AVGCostEach] ),
        FILTER ( B, B[PartSupplier] = MAX ( A[PartSupplier] ) && B[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] ) )
    )
RETURN
    IF ( SUM ( A[AVGCostEach] ) <= _MinAvg_B, SUM ( A[AVGCostEach] ), _MinAvg_B )

Result:

2.     Savings from Same Part Different Supplier (output the smaller avg and the new supplier in A/B with same part and different supplier with A)

2_Supplier = 
VAR _MinAvg_B =
    CALCULATE (
        MIN ( B[AVGCostEach] ),
        FILTER (
            B,
            B[PartSupplier] <> MAX ( A[PartSupplier] )
                && B[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] )
        )
    )
RETURN
    IF (
        _MinAvg_B = BLANK (),
        MAX ( A[PartSupplier] ),
        IF (
            SUM ( A[AVGCostEach] ) <= _MinAvg_B,
            MAX ( A[PartSupplier] ),
            CALCULATE (
                MAX ( B[PartSupplier] ),
                FILTER (
                    B,
                    B[PartSupplier] <> MAX ( A[PartSupplier] )
                        && B[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] )&&B[AVGCostEach]=_MinAvg_B
                )
            )
        )
    )

2_Avg = 
VAR _MinAvg_B =
    CALCULATE (
        MIN ( B[AVGCostEach] ),
        FILTER (
            B,
            B[PartSupplier] <> MAX ( A[PartSupplier] )
                && B[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] )
        )
    )
RETURN
    IF (
        _MinAvg_B = BLANK (),
        SUM ( A[AVGCostEach] ),
        IF ( SUM ( A[AVGCostEach] ) <= _MinAvg_B, SUM ( A[AVGCostEach] ), _MinAvg_B )
    )

Result:

3.     Saving based on Alternate Part, Same Supplier (output the smaller avg and the Ap in A/C with alternate part and same supplier with A)

3_AP = 
VAR _MinAvg_C =
    CALCULATE (
        MIN ( C[AVGCostEach] ),
        FILTER ( C, C[PartSupplier] = MAX ( A[PartSupplier] ) && C[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] ) )
    )
RETURN
    IF (
        _MinAvg_C = BLANK (),
        BLANK (),
        IF (
            SUM ( A[AVGCostEach] ) <= _MinAvg_C,
            BLANK (),
            CALCULATE (
                MAX ( C[Alternate Part] ),
                    FILTER ( C, C[PartSupplier] = MAX ( A[PartSupplier] ) &&  C[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] )  )
                )
        )
)

3_Avg = 
VAR _MinAvg_C =
    CALCULATE (
        MIN ( C[AVGCostEach] ),
        FILTER ( C, C[PartSupplier] = MAX ( A[PartSupplier] ) && C[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] ) )
    )
RETURN
    IF(_MinAvg_C=BLANK(), SUM ( A[AVGCostEach] ),
    IF ( SUM ( A[AVGCostEach] ) <= _MinAvg_C, SUM ( A[AVGCostEach] ), _MinAvg_C ))

Result:

4.     Savings based on Alternate Part, Alternate Supplier (output the smaller avg and the Ap/new supplier in C with alternate part and different supplier with A)

4_Supplier = 
VAR _MinAvg_C =
    CALCULATE (
        MIN ( C[AVGCostEach] ),
        FILTER ( C, C[PartSupplier] <> MAX ( A[PartSupplier] ) && C[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] ) )
    )
RETURN
    IF (
        _MinAvg_C = BLANK (),
        MAX(A[PartSupplier]),
        IF (
            SUM ( A[AVGCostEach] ) <= _MinAvg_C,
            MAX(A[PartSupplier]),
                       CALCULATE (
                MAX ( C[PartSupplier] ),
                    FILTER ( C, C[PartSupplier] <> MAX ( A[PartSupplier] ) && C[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] ) && C[AVGCostEach]=_MinAvg_C)
                )
        )
)

4_AP = 
VAR _MinAvg_C =
    CALCULATE (
        MIN ( C[AVGCostEach] ),
        FILTER ( C, C[PartSupplier] <> MAX ( A[PartSupplier] ) && C[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] ) )
    )
RETURN
    IF (
        _MinAvg_C = BLANK (),
        BLANK (),
        IF (
            SUM ( A[AVGCostEach] ) <= _MinAvg_C,
            BLANK (),
            CALCULATE (
                MAX ( C[Alternate Part] ),
                    FILTER ( C, C[PartSupplier] <> MAX ( A[PartSupplier] ) && C[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] ) )
                )
        )
)

4_Avg = 
VAR _MinAvg_C =
    CALCULATE (
        MIN ( C[AVGCostEach] ),
        FILTER ( C, C[PartSupplier] <> MAX ( A[PartSupplier] ) && C[PrimaryPartConcat] = MAX ( A[PrimaryPartConcat] ) )
    )
RETURN
    IF(_MinAvg_C=BLANK(), SUM ( A[AVGCostEach] ),
    IF ( SUM ( A[AVGCostEach] ) <= _MinAvg_C, SUM ( A[AVGCostEach] ), _MinAvg_C ))

Result:

If this reply still couldn’t help you to solve your problem, please provide me the complete data or provide me with your pbix file by onedrive for business.

You can download the pbix file from this link: Calculated Measures based on conditions across tables

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[Anonymous]

Thank you @v-rzhou-msft !

I created the individual measures, but when I try putting them into a table for totals, the grand total is wrong. I tried creating this summary table, but it is not working right. Any suggestion?

Summary Savings = 
SUMMARIZE('Shop Details','Shop Details'[PrimaryPartColumn],
"Cost Savings 1st" , ([1. Measure for Cost Savings]),
"Cost Savings 2nd" , ([2. Measure for Cost Savings]),
"Cost Savings 3rd" , ([3. Measure for Cost Savings]),
"Cost Savings 4th" , ([4. Measure for Cost Savings]),
)