Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Creating a Measure with dynamic computation depending on another column's value

I am trying to create a matrix in the following format, where the values is just 1 measure (computed from Volume column).

Expected result:

Sample data:

 

This is the code I came up with. The three columns, Beginning Inventory, Volume Received, and Variance, KL, all work as expected. But the Variance % does not show any values < variance % = _variance / (_begINV + volRCV) >. I believe this is due to the filters applied in the first 3 variables that interferes with the last variable. I would like to know if there is a way to revise my code to achieve this or should I just create 3 measures?

 

 

In-Plant Volume = 
VAR _begINV = 
    CALCULATE(
        SUM('Terminal In-Plant Performance'[Volume]),
        'Terminal In-Plant Performance'[Attribute] = "Beginning Inventory, KL",
        'Calendar'[Date] = MIN('Calendar'[Date])
    )
VAR _volRCV = 
    CALCULATE(
        SUM('Terminal In-Plant Performance'[Volume]),
       'Terminal In-Plant Performance'[Attribute] = "Volume Received, KL"
    )
VAR _variance = 
    CALCULATE(
        SUM('Terminal In-Plant Performance'[Volume]),
        'Terminal In-Plant Performance'[Attribute] = "Variance, KL"
    )

RETURN  
SWITCH(
    SELECTEDVALUE('Terminal In-Plant Performance'[Attribute]),
    "Beginning Inventory, KL",
    _begINV,
    "Variance, KL",
    _variance,
    "Volume Received, KL",
    _volRCV,
    "Variance (%)",
    FORMAT(
        DIVIDE(
            FILTER(ALL('Terminal In-Plant Performance'[Attribute]),_variance),
            FILTER(ALL('Terminal In-Plant Performance'[Attribute]),_begINV) + FILTER(ALL('Terminal In-Plant Performance'[Attribute]),_volRCV),
            "-"
        ),
        "Percent"
    ),
    SUM('Terminal In-Plant Performance'[Volume])
)

 

 

 

 

2 Replies