cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Sean Community Champion
Community Champion

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

@rynoh17 Add ALLSELECTED(Table1) like this...

 

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 )
Vvelarde Community Champion
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

View solution in original post

Sean Community Champion
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 )

 

View solution in original post

Vvelarde Community Champion
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.

 

Thanks for your observation.




Lima - Peru
Sean Community Champion
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

 

% Margin per Region ALLSELECTED.png

View solution in original post

Helpful resources

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

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

And the winner is...

Announcing the New Spanish Forum

Announcing the New Spanish Forum

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

April 2020 Community Highlights

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

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

Top Solution Authors
Top Kudoed Authors