Forum Discussion

IgorM's avatar
IgorM
Frequent Visitor
3 years ago
Solved

Creating a temporary (VAR) table and filtering it

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 f...
  • AlexisOlson's avatar
    AlexisOlson
    3 years ago

    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] )
    )
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
3 years ago

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