Combining Measures from different Tables

Question

I'm currently building a dashboard that consist of data sources with multiple level of relationship. Some of the tables do not communicate with each other and do not have a relationship that I can combine.

Table Relationship

What I'm trying to achieve is to combine all 3 measures into 1 table. Ideally, the intended calculated measure I'm looking for is to get something like this.

Overall Yield = Yield A (From Table A) * Yield B (From Table B) * Yield C (From Table C).

I tried to create a relationship across the 3 tables but as they don't talk to each other, the results being shown over is not correct and is showing one single value across day, week & month. I also tried to play around by linking the 3 tables into a master calendar table and putting the combined measure there, but it's not working as well. My thought process here is to combine the 3 measures into 1 and store this combined measure in a calendar table so that I can use the Date from Calendar table as the main Date column. However, I'm not really sure how I can navigate this, and this is where I'm stuck at.

The expected output I'm expecting to retrieve from my data source is something like this.

Output

Answer 1

pls modify your measures

Yield A Formula =
CALCULATE (
    SUM ( 'Yield A'[FINISHQTY] ),
    FILTER (
        'Yield A',
        'Yield A'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
    )
)
    / CALCULATE (
        SUM ( 'Yield A'[STARTQTY] ),
        FILTER (
            'Yield A',
            'Yield A'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
        )
    )


Yield B Formula =
CALCULATE (
    SUM ( 'Yield B'[FINISHQTY] ),
    FILTER (
        'Yield B',
        'Yield B'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
    )
)
    / CALCULATE (
        SUM ( 'Yield B'[STARTQTY] ),
        FILTER (
            'Yield B',
            'Yield B'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
        )
    )

NEW START QTY =
CALCULATE (
    SUM ( 'Yield C'[NEW FINISH QTY] ),
    FILTER (
        'Yield C',
        'Yield C'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
    )
)
    + CALCULATE (
        SUM ( 'Yield C'[BNU Qty] ),
        FILTER (
            'Yield C',
            'Yield C'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
        )
    )

Yield C Formula =
CALCULATE (
    SUM ( 'Yield C'[NEW FINISH QTY] ),
    FILTER (
        'Yield C',
        'Yield C'[Week Number] = SELECTEDVALUE ( 'Calendar'[Week Number] )
    )
) / [NEW START QTY]

then create a new measure

MEASURE =
VAR _a =
    IF ( ISBLANK ( 'Yield A'[Yield A Formula] ), 1, 'Yield A'[Yield A Formula] )
VAR _b =
    IF ( ISBLANK ( 'Yield B'[Yield B Formula] ), 1, 'Yield B'[Yield B Formula] )
VAR _c =
    IF ( ISBLANK ( 'Yield C'[Yield C Formula] ), 1, 'Yield C'[Yield C Formula] )
RETURN
    _a * _b * _c