- Power BI forums
- Updates
- News & Announcements
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Custom Visuals Development Discussion
- Health and Life Sciences
- Power BI Spanish forums
- Translated Spanish Desktop
- Power Platform Integration - Better Together!
- Power Platform Integrations
- Power Platform and Dynamics 365 Integrations
- Training and Consulting
- Instructor Led Training
- Galleries
- Community Connections & How-To Videos
- COVID-19 Data Stories Gallery
- Themes Gallery
- Data Stories Gallery
- R Script Showcase
- Webinars and Video Gallery
- Quick Measures Gallery
- 2021 MSBizAppsSummit Gallery
- 2020 MSBizAppsSummit Gallery
- 2019 MSBizAppsSummit Gallery
- Events
- Ideas
- Custom Visuals Ideas
- Issues
- Issues
- Events
- Upcoming Events
- Community Engagement
- T-Shirt Design Challenge 2023
- Community Blog
- Power BI Community Blog
- Custom Visuals Community Blog
- Community Support
- Community Accounts & Registration
- Using the Community
- Community Feedback

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

- Power BI forums
- Forums
- Get Help with Power BI
- Desktop
- Re: Aggregating Results From Multiple Variables Us...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

11-05-2021
05:39 PM

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

Solved! Go to Solution.

1 ACCEPTED SOLUTION

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-06-2021
12:54 PM

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

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-06-2021
12:22 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-06-2021
12:54 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-07-2021
10:31 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-08-2021
07:27 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-05-2021
08:17 PM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-06-2021
09:03 AM

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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

11-06-2021
09:56 AM

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:

Announcements

Check out the November 2023 Power BI update to learn about new features.

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Featured Topics