Creating a temporary (VAR) table and filtering it

[IgorM]

Hi,

I have a question about data modelling and/or DAX query.

Below can be found a simplified data model. This model is built in Excel (Power Pivot).

There is a Transactions table that is a fact table holding data from an accounting system.

The are also two tables (Areas and ConversionFactors) that currently have no relationships to any other tables.

What I need to do is create a report showing for every SegmentID and a finance year a margin amount calculated as follows:

• sum of the transactions for a given segment (i.e. sum of the amounts from the Value column from the Transactions table)

multiplied by

• the segment’s area for that year (SegmentArea from table Areas) multiplied by the segment’s conversion factor for the year (ConversionFacto from table ConversionFactors)

divided by

• the product of multiplying the areas of ALL the crops by their respective conversion factors for that year.

However I’m not sure how to calculate such an amount. I’ve tried using NATURALLEFTOUTERJOIN to create a temporary (VAR) table by joining Areas and ConversionFactors however there were two issues with that. Firstly, I was only able to join it by one column whereas here they need to be joined by Year and SegmentID. Secondly, I didn’t seem to be able to filter that temporary table to only get values for a current SegmentID.

Any ideas/suggestions will be much appreciated.

Kind regards,

[AlexisOlson · Accepted solution]

I would consider merging Areas and ConversionFactors into Transactions, so you have SegmentArea and ConversionFactor as columns in that table.

Then you could write something like this (untested):

VAR _Summary_ =
    CALCULATETABLE (
        SUMMARIZE (
            Transactions,
            Transactions[SegmentID],
            Transactions[SegmentArea],
            Transactions[ConversionFactor],
            "@SumValue", SUM ( Transactions[Value] )
        ),
        ALL ( Segments )
    )
VAR _CurrSubtable_ =
    FILTER ( _Summary_, Transactions[SegmentID] IN VALUES ( Segments[SegmentID] ) )
RETURN
    DIVIDE (
        SUMX ( _CurrSubtable_, [@SumValue] * [SegmentArea] * [ConversionFactor] ),
        SUMX ( _Summary_,      [@SumValue] * [SegmentArea] * [ConversionFactor] )
    )