Two tables joined by union how to divide by values in same column

[niel_orvyn1]

Hi there,

I'm trying to create an "quicker" measure so I can add Amount per Liter as a column next to my Income Statement.
I have two tables, one table with IS information (Sales; COS etc.) and the other with Volumes, they were joined with a union. The last mentioned are the "top levels" in the hierarchy.

Please see below example, Im trying to filter by volume and dividing the result into rest of the rows.
All seems to be working fine until I add additional columns. Obviously im not from a data background,
would anyone be able to assist in explaining why this is happening? Can the measure be changed to make it work?

Best Regards,

Niel

[v-cazheng-msft · Accepted solution]

Hi @niel_orvyn1 

Not very clear about how you join these tables by Union and the relationships between your tables. Does 'segmen 0 Code' column in the 'factGLTransactions' table?  You can try to make some changes to your Measure.

R/L tester =

DIVIDE (

    CALCULATE (

        SUM ( 'factGLTransactions'[Amount] ),

        'factGLTransactions'[AccounType] = "1. Volume"

    ),

    CALCULATE (

        SUM ( 'factGLTransaction'[Amount] ),

        ALLEXCEPT (

            'factGLTransactions',

            'factGLTransactions'[AccountType],

            [segment 0 Code]

        )

    )

)

If you still have questions, please don't hesitate to let me known and please describe the table structure and expected result clearer.

Best Regards

Caiyun Zheng

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!