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

     

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: