How to extract the resulted values from the matrix table to create anothe SUMX measure

Question

What I am trying to do is to take out from the table below, just the result from Madrid (70,89%) and Barcelona (83,92%) and consolidate both results weighting them according to "total production" measure.

The expected result would be the following = ( 70,89% x 52.550 + 83,92 x 135.100 ) / ( 52.550 + 135.100 ) = 80,27%

Here are the measures that I have created to build my matrix table

Total Production = sum(Database[Production])

Total Working Hours = sum(Database[Working Hours])

TotalExpectedProduction = sum(Database[Expected Production])

Avexpectedprod = divide(sumx(Database;[TotalExpectedProduction]*[Total Working Hours]);sum(Database[Working Hours]))

YTD Productivity =
CALCULATE (
    DIVIDE (
        SUMX (
            SUMMARIZE (
                Database;
                Database[Matchine];
                "AA"; [Total Production] / ( [Total Working Hours] * [Avexpectedprod] )
            );
            [AA] * [Total Working Hours]
        );
        [Total Working Hours];
        0
    );
    DATESYTD ( Calendar[Date]; "30/06" );
    FILTER (
        ALL ( 'Calendar' );
        'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
            && 'Calendar'[Fiscal Year] = MAX ( 'Calendar'[Fiscal Year] )
    )
)

Here my data table structure data table Here the matrix table and the expected result Image

Thank you so much for you Support RBN

score 0 · Answer 1 · answered Sep 21 '19 at 16:02

Done!!!

New2 = DIVIDE ( CALCULATE ( SUMX ( VALUES ( Database[Location] ); [YTD Productivity] * [Total Production] ); Database[Location] = "Barcelona" || Database[Location] = "Madrid" ); CALCULATE ( SUM ( Database[Production] ); Database[Location] = "Barcelona" || Database[Location] = "Madrid" ) )

How to extract the resulted values from the matrix table to create anothe SUMX measure

1 Answers1