Visualizing with static and dynamic measures

[Switto]

Hi Team,

I am working on a visual to compare the FTEs for the Base and Current year.  Base year FTE will never change however current year FTE will change(with slicer).

For the base FTE measure:

mBase_FTE = CALCULATE(SUM('Region Penetration'[Total FTE]),

USERELATIONSHIP(Dim_Region[Region],'Region Penetration'[Region]))

For the current year measure:

mCurrent_FTE = SUM(Table[Current FTE])

after putting both the measures in the value field, it is coming as per the requirement however, while selecting the FY Year or other slicers, the "mBase_FTE" measure is also changing.

Is there a way to make the "mBase_FTE" static and don't change with slicers?

Thanks

S

[Gabriel_Walkman · Accepted solution]

Do you use a separate calendar table? If yes, here's a suggestion:

mBase_FTE =

CALCULATE(

    SUM('Region Penetration'[Total FTE]),
    USERELATIONSHIP(Dim_Region[Region],'Region Penetration'[Region])),

    ALL( 'Calendar' )

)

If not (you should), then replace Calendar with whatever contains your year column, ie. 'Region Penetration'[FY Year]

Keep in mind that USERELATIONSHIP can result in trouble if you use RLS.

[Switto]

thanks for the quick reply @Gabriel_Walkman. The solution is working and thanks for the tip 🙂