cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

Create measure using factors from table.

Hi!

I´ve created this table containing all my my processes and corresponding factors. I want to use factors for each process beacuse when im forecasting i tend to see trends changing on process level.

So I´ve created my measure:

"ForeCastWithFactors = [Forecast] * CALCULATE(SUMX('ProcessFactors'; 'ProcessFactors'[ProcessFactor]))"

and row by row it works as expected, it increases the forecast by the factor (they are all 1.07 at the time)

However the problem is the total. its Obviously calculating the sum of all the factors x sum of forecast. and thus its not really usable in a bar chart for example.

Anyone know how to rewrite this so it works?

I did get help but the measure does not fill my entire calendar. Only until the end of april (Where my current data ends)

My measure for "Forecast" is created like this:

Measure: Result2016 = CALCULATE([Total Offered]; DATEADD('Dates'[Date]; -728; DAY))

Measure: Result2017 = CALCULATE([Total Offered]; DATEADD('Dates'[Date]; -364; DAY))

I´ve mixed with the number of days to get the weekends to line up for this year.

Measure: Forecast =

Var D1 = [Result2016]
Var D2 = [Result2017]

RETURN
DIVIDE(D1 + D2; 2; 0)

The proposed solution was this one and it does work like a charm and does what it is expected to do. Except that it seems to calculate only until my last record in April, see last picture.

Measure: ForeCastWithFactors =  SUMX('ProcessFactors'; [forecast] * ‘ProcessFactors'[ProcessFactor])

2 REPLIES 2
Highlighted
Super User IV

Re: Create measure using factors from table.

Hey,

ForeCastWithFactors =
CALCULATE(
SUMX('ProcessFactors'[Process],
'ProcessFactors'[ProcessFactor]* [Forecast]
)
)

This creates the product for each Row filtered by the Provess

Hopefully this will get you started

Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Frequent Visitor

Re: Create measure using factors from table.

Hi! Sorry for my bad english, hopefully this will clear things up a bit.

My current problem is this measure:

ForeCastWithFactors = SUMX('ProcessFactors'; [forecast] * 'ProcessFactors'[ProcessFactor]) It calculates correctly but it does not

fill my entire calendar for some reason. It only calculates until the end of April. I have no actual data after April.

My first measure "Forecast" however works fine and fills my entire calendar.

Measure: Forecast =

Var D1 = [Result2016]
Var D2 = [Result2017]

RETURN
DIVIDE(D1 + D2; 2; 0)