Measure from two tables

[Oleg222]

Hi Everyone. Please help with calculation. Calculation based on two tables. In one we take a percentage, in the second we take the volume and calculate by weighted average. Рroblem is in the final indicator. The measure considers it as the sum of percent multiplied by the sum of the volume. It is necessary that the calculation be saved as in rows.

The total should be (26.4*908840 + 26.4*693600 + 26.6*705160 + 26.6*53860) / (908840+696600+705160+53860) = 26.48738

Calculation with IF in the denominator is needed to calculate the volume when there was a percentage.

https://drive.google.com/file/d/1EbYITQGM64aIvczJfZDPbEC74S87M_Ql/view?usp=sharing

[Oleg222]

@amitchandak  result 53,00

[amitchandak]

@Oleg222 , change this one

measure, % = 
VAR First_P = 
DIVIDE (
    SUMX (
        VALUES ( 'Percent'[DateBranchProcess] ),
        CALCULATE (
            SUM( 'Percent'[Value, %]),
            'Percent'[Process] = "pr_1",
            'Percent'[Process level] = "_1",
            'Percent'[Half] = "1"
        )
            * CALCULATE (
                SUM ( 'Value'[Value, kg] ),
                'Value'[Process] = "pr_1",
                'Value'[Process level] = "_1",
                'Value'[Half] = "1"
            )
    ),
    SUMX (
        VALUES ( 'Percent'[DateBranchProcess] ),
        IF (
            CALCULATE (
                SUM( 'Percent'[Value, %]),
                'Percent'[Process] = "pr_1",
                'Percent'[Process level] = "_1",
                'Percent'[Half] = "1"
            ),
            1
        )
            * CALCULATE (
                SUM ( 'Value'[Value, kg] ),
                'Value'[Process] = "pr_1",
                'Value'[Process level] = "_1",
                'Value'[Half] = "1"
            )
    )
)

VAR Second_P = 
DIVIDE (
    SUMX (
        VALUES ( 'Percent'[DateBranchProcess] ),
        CALCULATE (
            SUM( 'Percent'[Value, %]),
            'Percent'[Process] = "pr_1",
            'Percent'[Process level] = "_1",
            'Percent'[Half] = "2"
        )
            * CALCULATE (
                SUM ( 'Value'[Value, kg] ),
                'Value'[Process] = "pr_1",
                'Value'[Process level] = "_1",
                'Value'[Half] = "2"
            )
    ),
    SUMX (
        VALUES ( 'Percent'[DateBranchProcess] ),
        IF (
            CALCULATE (
                SUM( 'Percent'[Value, %]),
                'Percent'[Process] = "pr_1",
                'Percent'[Process level] = "_1",
                'Percent'[Half] = "2"
            ),
            1
        )
            * CALCULATE (
                SUM ( 'Value'[Value, kg] ),
                'Value'[Process] = "pr_1",
                'Value'[Process level] = "_1",
                'Value'[Half] = "2"
            )
    )
)

VAR First_V = 
    SUMX (
        VALUES ( 'Percent'[DateBranchProcess] ),
        IF (
            CALCULATE (
                SUM( 'Percent'[Value, %]),
                'Percent'[Process] = "pr_1",
                'Percent'[Process level] = "_1",
                'Percent'[Half] = "1"
            ),
            1
        )
            * CALCULATE (
                SUM ( 'Value'[Value, kg] ),
                'Value'[Process] = "pr_1",
                'Value'[Process level] = "_1",
                'Value'[Half] = "1"
            )
    )
VAR Second_V = 
    SUMX (
        VALUES ( 'Percent'[DateBranchProcess] ),
        IF (
            CALCULATE (
                SUM( 'Percent'[Value, %]),
                'Percent'[Process] = "pr_1",
                'Percent'[Process level] = "_1",
                'Percent'[Half] = "2"
            ),
            1
        )
            * CALCULATE (
                SUM ( 'Value'[Value, kg] ),
                'Value'[Process] = "pr_1",
                'Value'[Process level] = "_1",
                'Value'[Half] = "2"
            )
    )

RETURN
sumx(ADDCOLUMNS(SUMMARIZE('Value','Calendar'[Date]),"_1",DIVIDE(First_P * First_V + Second_P * Second_V, First_V + Second_V)),[_1])