Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Anonymous
Not applicable

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:

slfollosco_0-1690358550625.png

Sample data:

slfollosco_1-1690358679597.png

 

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 2
amitchandak
Super User
Super User

@Anonymous , Try like

 

calculate(
DIVIDE(
_variance,
_begINV + _volRCV
), REMOVEFILTERS('Terminal In-Plant Performance'[Attribute]))

 

 

For formattin refer dynamic string formatting

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-new-dynamic-format-strings-for-measures/

Anonymous
Not applicable

I tried this. Unfortunately, it didn't work. the whole Variance % column did not show up. the rest of the columns worked perfectly fine.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.