Calculated Measure for Yearly Cumulative % Across Months in a visual

[Anonymous]

Hey,

I have data from January to September of this year. I have a bar chart and would like a static line for the margin % for the whole year. I know how to do it for the entire company by creating a column, but then it doesn't change for drill downs. The issue is that I want it to change for the roles it is shared with. ie, I want the static line to be Region specific for the region it is shared with. I want the line to be District specific for the district I share with, not just the company margin. Please help. Thanks

[Vvelarde · Accepted solution]

@Anonymous

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

[Sean · Accepted solution]

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