Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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's avatar
    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

     

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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's avatar
      AlexisOlson
      Super User
      4 years ago

      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: