Creating a measure relating data

[ephramz]

Hi, I have two tables which have columns (Strategy) and (Package) which contains related data.

I wish to create a measure, to compare/relate the columns in strategy and package between both table and use the 'correct time' column to minus the 'average time' column. How can I do that ?

E.g if Table 2 and Table 1 has the same Strategy and package names/codes, 'SL2120' Strategy and 'AA' Package, take the value 15.3 minus 14.3 which should give 1.0.

Table 1			Table 2
Strategy	Package	Average Time		Strategy	Package	Corrected Time
SL2120	AA	14.3		SL2120	AA	15.3
SL2123	AB	12.4		SL2123	AB	14.5
SL2124	AC	12.5		SL2124	AC	12.2
SL2125	AD	12.5		SL2125	AD	15.4
SL2126	AE	12.6		SL2126	AE	16.4

[AlB · Accepted solution]

Hi @ephramz 

1. Place Table1[Strategy] and Table1[Package] in a table visual

2. Place this measure in the visual

See it all at work in the attached file.

Measure = 
VAR avgTime_ = SELECTEDVALUE ( Table1[Average Time] )
VAR corrTime_ =
    CALCULATE (
        MAX ( Table2[Corrected Time] ),
        TREATAS (
            SUMMARIZE ( Table1, Table1[Strategy], Table1[Package] ),
            Table2[Strategy],
            Table2[Package]
        )
    )
RETURN
corrTime_ - avgTime_

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.