cancel
Showing results for
Did you mean:
Community Champion

## Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

```Margin_Region 2 =
VAR RegionSales =
CALCULATE ( SUM ( Table1[Sales] ), VALUES ( Table1[Region] ), ALLSELECTED(Table1) )
VAR RegionCost =
CALCULATE ( SUM ( Table1[Cost] ), VALUES ( Table1[Region] ), ALLSELECTED(Table1) )
RETURN
DIVIDE ( RegionSales - RegionCost, RegionSales )```
Community Champion

## Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@rynoh17

hello, to create a fixed margin:

```Margin =
DIVIDE (
CALCULATE (
SUM ( Table1[Sales] ),
ALLEXCEPT ( Table1, Table1[Region], Table1[District] )
)
- CALCULATE (
SUM ( Table1[Cost] ),
ALLEXCEPT ( Table1, Table1[Region], Table1[District] )
),
CALCULATE (
SUM ( Table1[Sales] ),
ALLEXCEPT ( Table1, Table1[Region], Table1[District] )
)
)```

Lima - Peru
Community Champion

## Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@Vvelarde That works too - But why do we need the District?

```Margin 2 =
DIVIDE (
CALCULATE (
SUM ( Table1[Sales] ),
ALLEXCEPT ( Table1, Table1[Region] )
)
- CALCULATE (
SUM ( Table1[Cost] ),
ALLEXCEPT ( Table1, Table1[Region] )
),
CALCULATE (
SUM ( Table1[Sales] ),
ALLEXCEPT ( Table1, Table1[Region] )
)
)```

EDIT: You can actually revise mine to only this as well

```Margin_Region =
VAR RegionSales =
CALCULATE ( SUM ( Table1[Sales] ), ALLSELECTED(Table1[Month]) )
VAR RegionCost =
CALCULATE ( SUM ( Table1[Cost] ), ALLSELECTED(Table1[Month]) )
RETURN
DIVIDE ( RegionSales - RegionCost, RegionSales )```

Community Champion

## Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@Sean

Yes, There is no need to add the district column the obtain the result.

Lima - Peru
Community Champion

## Re: Calculated Measure for Yearly Cumulative % Across Months in a visual

@rynoh17 Actually the more I test this the more I think you should go with this....

```Margin_Region ALS =
VAR RegionSales =
CALCULATE ( SUM ( Table1[Sales] ), ALLSELECTED(Table1[Month]) )
VAR RegionCost =
CALCULATE ( SUM ( Table1[Cost] ), ALLSELECTED(Table1[Month]) )
RETURN
DIVIDE ( RegionSales - RegionCost, RegionSales )```

Look the picture - you can test this with a matrix visualization - I think the above will give you what you are looking for

If you use Slicers andd a Month Slicer and select only a number of months this will adjust

Announcements

#### ‘Better Together’ T-Shirt Contest – Winner Announced!

And the winner is...

#### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

#### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights.

#### MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors