Aggregating Results From Multiple Variables Using SWITCH...Final Total Not Displaying?

Hi all - I'm working on a measure to be able to dynamically calculate results based on certain regions. I've already accomplished this for 5-6 other measures with similar complexity in the code ...

Need Help

Show and Tell

AlexisOlson

4 years ago

You can write measures that behave differently when rolled up using functions like HASONEVALUE, ISINSCOPE, ISFILTERED.

For example,

Distributable Margin =
VAR Regional_Margin = DIVIDE ( ... )
VAR GLOBAL_Margin = CALCULATE ( ... )
VAR Regional_Result = SWITCH ( ... )
VAR GLOBAL_Result = IF ( ... )
VAR Final_Result =
    IF ( ISINSCOPE ( Data[Region] ), Regional_Result, GLOBAL_Result )
RETURN
    Final_Result

Anonymous

Not applicable

4 years ago

Thanks, Alexis - This is super helpful, but there are instances where the global totals are calculated a little differently than the regional breakouts. Here's an example from the Distributable Margin % - Up to 20% Margin Tier measure I hadn't previously included. Since the logic in this measure is needed for the Distributable Margin $ Total I posted initially, it's probably worth taking a look at as well.

Distributable Margin - Up to 20% Margin Tier Updated DAX Logic Scale = 

--- ### CALCULATES AMER MARGIN ### ---

VAR AMER_Margin =
CALCULATE (
    DIVIDE (
        'Financial Measures'[Profit/(Loss)],
        'Financial Measures'[Revenue],
        0
    ),
    KEEPFILTERS(
        'Revenue & Costs Data'[Region] = "AMER"
    )
)

--- ### CALCULATES APAC MARGIN ### ---- 

VAR APAC_Margin =
CALCULATE (
    DIVIDE (
        'Financial Measures'[Profit/(Loss)],
        'Financial Measures'[Revenue],
        0
    ),
    KEEPFILTERS(
        'Revenue & Costs Data'[Region] = "APAC"
    )
)

--- ### CALCULATES EMEA MARGIN ### ---- 

VAR EMEA_Margin =
CALCULATE (
    DIVIDE (
        'Financial Measures'[Profit/(Loss)],
        'Financial Measures'[Revenue],
        0
    ),
    KEEPFILTERS(
        'Revenue & Costs Data'[Region] = "EMEA"
    )
)

--- ### CALCULATES GLOBAL MARGIN ### ----

VAR GLOBAL_Margin = 
    DIVIDE (
        'Financial Measures'[Profit/(Loss)],
        'Financial Measures'[Revenue],
        0
)

VAR AMER_Result = 
SWITCH (
    TRUE (),
    GLOBAL_Margin >= [Margin Measure]
        && AMER_Margin > [Margin Measure],
        AMER_Margin - [Margin Measure], 
    GLOBAL_Margin >= [Margin Measure]
        && AMER_Margin < [Margin Measure],
        AMER_Margin - [Margin Measure],
    GLOBAL_Margin < [Margin Measure] && AMER_Margin < [Margin Measure], AMER_Margin - [Margin Measure], 
    GLOBAL_Margin > 0
        && GLOBAL_Margin < [Margin Measure], AMER_Margin,
    AMER_Margin < 0, 0,
    GLOBAL_Margin <= [Margin Measure], AMER_Margin
)

--- ### APAC MARGIN RESULT ### ---

VAR APAC_Result = 
SWITCH (
    TRUE (),
    GLOBAL_Margin >= [Margin Measure]
        && APAC_Margin > [Margin Measure],
        APAC_Margin - [Margin Measure], 
    GLOBAL_Margin >= [Margin Measure]
        && APAC_Margin < [Margin Measure],
        APAC_Margin - [Margin Measure],
    GLOBAL_Margin < [Margin Measure] && APAC_Margin < [Margin Measure], APAC_Margin - [Margin Measure], 
    GLOBAL_Margin > 0
        && GLOBAL_Margin < [Margin Measure], APAC_Margin,
    APAC_Margin < 0, 0,
    GLOBAL_Margin <= [Margin Measure], APAC_Margin
)

--- ### EMEA MARGIN RESULT ### ---

VAR EMEA_Result = 
SWITCH (
    TRUE (),
    GLOBAL_Margin >= [Margin Measure]
        && EMEA_Margin > [Margin Measure],
        EMEA_Margin - [Margin Measure], 
    GLOBAL_Margin >= [Margin Measure]
        && EMEA_Margin < [Margin Measure],
        EMEA_Margin - [Margin Measure],
    GLOBAL_Margin < [Margin Measure] && EMEA_Margin < [Margin Measure], EMEA_Margin - [Margin Measure], 
    GLOBAL_Margin > 0
        && GLOBAL_Margin < [Margin Measure], EMEA_Margin,
    EMEA_Margin < 0, 0,
    GLOBAL_Margin <= [Margin Measure], EMEA_Margin
)

---- ### CALCULATES GLOBAL Result ### ---- 

VAR GLOBAL_Result =
IF (
    GLOBAL_Margin > [Margin Measure],
    IF (
        GLOBAL_Margin > [Margin Measure],
        GLOBAL_Margin - [Margin Measure],
        GLOBAL_Margin - [Margin Measure]
    ),
    IF (
        GLOBAL_Margin = [Margin Measure],
        IF (
            GLOBAL_Margin > [Margin Measure],
            GLOBAL_Margin - [Margin Measure],
            0
        ),
        IF (
            GLOBAL_Margin < [Margin Measure],
            GLOBAL_Margin,
            IF (
                GLOBAL_Margin = [Margin Measure],
                IF (
                    GLOBAL_Margin > [Margin Measure],
                    GLOBAL_Margin,
                    0
                ),
                IF (
                    GLOBAL_Margin > [Margin Measure], 
                    GLOBAL_Margin - [Margin Measure],
                    0
                )
            )
        )
    )
)

--- ### CALCULATES FINAL DISTRIBUTABLE MARGIN TIER UP TO 20% BY REGION, INCLUDING GLOBAL CALCULATION ### ---

VAR Final_Result =
SWITCH (
    MAX ( 'Revenue & Costs Data'[Region] ),
    "AMER", AMER_Result,
    "APAC", APAC_Result,
    "EMEA", EMEA_Result,
    "GLOBAL", GLOBAL_Result
)

RETURN 
    Final_Result

AlexisOlson

Super User

4 years ago

You can write measures that behave differently when rolled up using functions like HASONEVALUE, ISINSCOPE, ISFILTERED.

For example,

Distributable Margin =
VAR Regional_Margin = DIVIDE ( ... )
VAR GLOBAL_Margin = CALCULATE ( ... )
VAR Regional_Result = SWITCH ( ... )
VAR GLOBAL_Result = IF ( ... )
VAR Final_Result =
    IF ( ISINSCOPE ( Data[Region] ), Regional_Result, GLOBAL_Result )
RETURN
    Final_Result

Anonymous

Not applicable

4 years ago

Hi Alexis - Sorry for not responding earlier with a thank you when you answered my follow up question yesterday, but thank you!! This was extermely helpful and gave me a simpler way to think through the logic of how I'm approaching writing variables in DAX. I'm just getting started with this aspect of BI, so it's certainly helpful. Also, your code from earlier regarding the Distributable Margin amounts in $ was great, but I made some slight changes to the revenue variable so it was being properly calculated within the SUMX RETURN...see below. 🙂

Distributable Profit $ - Up to 20% Margin Tier = 
VAR Global_Margin =
    CALCULATE (
        DIVIDE (
            [Profit/(Loss)],
            [Revenue],
            0
        ),
        ALL ( 'Revenue & Costs Data'[Region] )
    )
VAR Cutoff = [Margin Measure]
RETURN
    CALCULATE (
        SUMX (
            VALUES ( 'Revenue & Costs Data'[Region] ),
            VAR CurrRegion = 'Revenue & Costs Data'[Region]
            VAR Margin =
                DIVIDE (
                    [Profit/(Loss)],
                    [Revenue],
                    0
                )
            VAR Revenue = 'Financial Measures'[Revenue]
            VAR Target_Variance =
                DIVIDE (
                    [Target Profit Variance],
                    [Revenue],
                    0
                )
            VAR DistMargin = [Distributable Margin - Up to 20% Margin Tier Updated DAX Logic]
            RETURN
                IF (
                    Global_Margin > Cutoff
                        && Target_Variance > 0,
                    Revenue * DistMargin
                )
        )
    )

AlexisOlson
Super User
4 years ago
Nice. I'm glad to see you got it working.

Forum Discussion

Aggregating Results From Multiple Variables Using SWITCH...Final Total Not Displaying?

Related content

Data Aggregation issue

Aggregate

Image display in Power bi report

Average monthly sales buy aggregated

How to Display Rows Without Aggregation in Table Visual?