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

[williamadams12]

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 with the expected, accurate results. But I'm having trouble with one the past few days. 

Essentially, each region needs its own series of measures that are used to derive the final result. At the end, I aggregate all of the specified regional results for the global result totals, of course, using a SWITCH statement. I'm including the screen grab of the DAX code and resulting measure in the table below with the subtotals so you can see the expected result I'm wanting in the blank highlighted box just above it on the far right hand column on the GLOBAL row. 

I'm not the super adept at using variables just yet - just started digging into them a little more recently. 

My Marco Russo tells me the "Region_Result" totals I'm attempting to aggregate at the end aren't derived as proper scalar values and therefore cannot be properly aggregated for use in a different variable for a different row context, but maybe I'm wrong? But it's Friday night and I've run out of ideas. 

Distributable Profit $ - Up to 20% Margin Tier New = 

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

--- ### CALCULATES REVENUE ### ---

VAR AMER_Revenue =
    CALCULATE (
        SUM ( 'Revenue & Costs Data'[Revenue] ),
        'Revenue & Costs Data'[Region] = "AMER"
    )
VAR APAC_Revenue =
    CALCULATE (
        SUM ( 'Revenue & Costs Data'[Revenue] ),
        'Revenue & Costs Data'[Region] = "APAC"
    )
VAR EMEA_Revenue =
    CALCULATE (
        SUM ( 'Revenue & Costs Data'[Revenue] ),
        'Revenue & Costs Data'[Region] = "EMEA"
    )
VAR GLOBAL_Revenue = AMER_Revenue + APAC_Revenue + EMEA_Revenue 

--- ### CALCULATES VARIANCE ### ---

VAR AMER_Target_Variance =
    CALCULATE (
        DIVIDE (
            'Financial Measures'[Target Profit Variance],
            'Financial Measures'[Revenue],
            0
        ),
        KEEPFILTERS ( 'Revenue & Costs Data'[Region] = "AMER" )
    )
VAR APAC_Target_Variance =
    CALCULATE (
        DIVIDE (
            'Financial Measures'[Target Profit Variance],
            'Financial Measures'[Revenue],
            0
        ),
        KEEPFILTERS ( 'Revenue & Costs Data'[Region] = "APAC" )
    )
VAR EMEA_Target_Variance =
    CALCULATE (
        DIVIDE (
            'Financial Measures'[Target Profit Variance],
            'Financial Measures'[Revenue],
            0
        ),
        KEEPFILTERS ( 'Revenue & Costs Data'[Region] = "EMEA" )
    )
VAR GLOBAL_Target_Variance =
    DIVIDE (
        'Financial Measures'[Target Profit Variance],
        'Financial Measures'[Revenue],
        0
    ) 

--- ### DISTRIBUTABLE PROFIT MARGINS ### ---
VAR AMER_DistMargin =
    CALCULATE (
        [Distributable Margin - Up to 20% Margin Tier Updated DAX Logic Scale],
        FILTER (
            'Revenue & Costs Data',
            'Revenue & Costs Data'[Region] = "AMER"
        )
    )
VAR APAC_DistMargin =
    CALCULATE (
        [Distributable Margin - Up to 20% Margin Tier Updated DAX Logic Scale],
        FILTER (
            'Revenue & Costs Data',
            'Revenue & Costs Data'[Region] = "APAC"
        )
    )
VAR EMEA_DistMargin =
    CALCULATE (
        [Distributable Margin - Up to 20% Margin Tier Updated DAX Logic Scale],
        FILTER (
            'Revenue & Costs Data',
            'Revenue & Costs Data'[Region] = "EMEA"
        )
    )
VAR GLOBAL_DistMargin = [Distributable Margin - Up to 20% Margin Tier Updated DAX Logic Scale]

VAR AMER_Result =
    SWITCH (
        TRUE (),
        GLOBAL_Margin >= [Margin Measure]
            && AMER_Target_Variance > 0,
            AMER_Revenue * AMER_DistMargin,
        GLOBAL_Margin >= [Margin Measure]
            && AMER_Target_Variance < 0, 0,
        GLOBAL_Margin < [Margin Measure]
            && GLOBAL_Margin >= 0, 0
    )
VAR APAC_Result =
    SWITCH (
        TRUE (),
        GLOBAL_Margin >= [Margin Measure]
            && APAC_Target_Variance > 0,
            APAC_Revenue * APAC_DistMargin,
        GLOBAL_Margin >= [Margin Measure]
            && APAC_Target_Variance < 0, 0,
        GLOBAL_Margin < [Margin Measure]
            && GLOBAL_Margin >= 0, 0
    )
VAR EMEA_Result =
    SWITCH (
        TRUE (),
        GLOBAL_Margin >= [Margin Measure]
            && EMEA_Target_Variance > 0,
            EMEA_Revenue * EMEA_DistMargin,
        GLOBAL_Margin >= [Margin Measure]
            && EMEA_Target_Variance < 0, 0,
        GLOBAL_Margin < [Margin Measure]
            && GLOBAL_Margin >= 0, 0
    )
    
VAR GLOBAL_Result =  AMER_Result + EMEA_Result + APAC_Result

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

[williamadams12]

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 · Accepted solution]

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

[williamadams12]

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]

Nice. I'm glad to see you got it working.

[AlexisOlson]

I think the issue is that when you are in the GLOBAL region filter context (the highlighted row), all of the components, AMER_Result + EMEA_Result + APAC_Result, are zero or blank.

For example, in this filter context the following returns blank:

VAR AMER_DistMargin =
    CALCULATE (
        [Distributable Margin - Up to 20% Margin Tier Updated DAX Logic Scale],
        FILTER ( 'Revenue & Costs Data', 'Revenue & Costs Data'[Region] = "AMER" )
    )

 This is because the Region filter context is GLOBAL and it can't be AMER and GLOBAL at the same time.

I really don't think you need to calculate each region with its own measures and variables. You can iterate over them instead, in which case your measure might simplify to something more like this (not tested):

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

[williamadams12]

Thanks, but I think the problem with this approach is that the GLOBAL designation isn't a region, it's just a designated value in the region field to aggregate on other regions. For example: 

Revenue =

VAR AMER_Revenue =
    CALCULATE (
        SUM ( 'Revenue & Costs Data'[Revenue] ),
        'Revenue & Costs Data'[Region] = "AMER"
    )

VAR APAC_Revenue =
    CALCULATE (
        SUM ( 'Revenue & Costs Data'[Revenue] ),
        'Revenue & Costs Data'[Region] = "APAC"
    )

VAR EMEA_Revenue =
    CALCULATE (
        SUM ( 'Revenue & Costs Data'[Revenue] ),
        'Revenue & Costs Data'[Region] = "EMEA"

VAR GLOBAL_Revenue = AMER_Revenue + APAC_Revenue + EMEA_Revenue

VAR Result =
    SWITCH (
        MAX ( 'Revenue & Costs Data'[Region] ),
        "AMER", AMER_Revenue,
        "APAC", APAC_Revenue,
        "EMEA", EMEA_Revenue,
        "GLOBAL", GLOBAL_Revenue
    )
RETURN
    Result

[AlexisOlson]

I get that but you should still be able to write it without cases for each region.

For this simpler measure, it might look like this:

Revenue =
IF (
    SELECTEDVALUE ( 'Revenue & Costs Data'[Region] ) = "GLOBAL",
    CALCULATE (
        SUM ( 'Revenue & Costs Data'[Revenue] ),
        ALL ( 'Revenue & Costs Data'[Region] )
    ),
    SUM ( 'Revenue & Costs Data'[Revenue] )
)

Is there a reason you need GLOBAL as another row rather than as a total? It's a headache having to have a separate case everywhere when you can just rename the total row like this: