Forum Discussion

Oleg222's avatar
Oleg222
Helper II
4 years ago

Measure from two tables

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

 

Need Help

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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])