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

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now! Learn more

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
Power BI DataViz World Championships

Power BI Dataviz World Championships

The Power BI Data Visualization World Championships is back! Get ahead of the game and start preparing now!

December 2025 Power BI Update Carousel

Power BI Monthly Update - December 2025

Check out the December 2025 Power BI Holiday Recap!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.