Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
CatManKuhn
Helper II
Helper II

Combine Multiple Measures Into Single Measure

Hi all, 

 

I am attempting to clean up some formulas due to poor performance. I am working on creating a headcount forecast using monthly headcount data, turnover data, and a backfill rate. I have 13 measures that determine the headcount forecast for 12 months in the future. I would like to combine these 12 into a single measure to see if this improves performance. Here is an image of 3 of these formulas and how I would like to display them in a single measure called TEST Forecast:

CatManKuhn_0-1690298595009.png

 

I would like TEST Forecast to display the values in the Headcount Forecast Turnover + Backfill Count Helper columns. 

 

Here is the DAX for Headcount Forecast Turnover + Backfill Count Helper:

 

Headcount Forecast Turnover + Backfill Count Helper = 
var HeadcountForecastPM = CALCULATE (
    [Headcount Actual],
    FILTER (ALL('Date'),
        'Date'[Serial Month]
            = MAX ( 'Date'[Serial Month] ) - 1
    )
)

var TurnoverForecast = HeadcountForecastPM * [Monthly Turnover Rate 3Y Average]

var Backfill = TurnoverForecast * [Backfill Percent Value]

var CurrentMonthForecast = IF ( [CurrentMonthYear] = YEAR(TODAY())&MONTH(TODAY()), HeadcountForecastPM - TurnoverForecast + Backfill)

Return
CurrentMonthForecast


Here is the DAX for Headcount Forecast Turnover + Backfill Count Helper 2:

Headcount Forecast Turnover + Backfill Count Helper 2 = 
var HeadcountForecastPM = CALCULATE (
    [Headcount Forecast Turnover + Backfill Count Helper],
    FILTER (ALL('Date'),
        'Date'[Serial Month]
            = MAX ( 'Date'[Serial Month] ) - 1
    )
)

var TurnoverForecast = HeadcountForecastPM * [Monthly Turnover Rate 3Y Average]

var Backfill = TurnoverForecast * [Backfill Percent Value]

var HeadcountForecast = HeadcountForecastPM - TurnoverForecast + Backfill

RETURN
HeadcountForecast

 

Lastly, here is my formula for TEST Forecast:

TEST Forecast = 
var HeadcountForecastPM = CALCULATE (
    [Headcount Actual],
    FILTER (ALL('Date'),
        'Date'[Serial Month]
            = MAX ( 'Date'[Serial Month] ) - 1
    )
)

var TurnoverForecast = HeadcountForecastPM * [Monthly Turnover Rate 3Y Average]

var Backfill = TurnoverForecast * [Backfill Percent Value]

var CurrentMonthForecast = IF ( [CurrentMonthYear] = YEAR(TODAY())&MONTH(TODAY()), HeadcountForecastPM - TurnoverForecast + Backfill)

--------------------------------------

var TurnoverForecast2 = CurrentMonthForecast * [Monthly Turnover Rate 3Y Average]

var Backfill2 = TurnoverForecast2 * [Backfill Percent Value]

var HeadcountForecast = CurrentMonthForecast - TurnoverForecast2 + Backfill2

--------------------------------------
Return 
CurrentMonthForecast

Anytime I sum the variables I will get a true sum, but I want them to be appended so that the column shows a value for each month rather than a sum for the current month.

 

Any help is greatly appreciated!

0 REPLIES 0

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

Find out what's new and trending in the Fabric community.