What's wrong with my formula

[RonaldvdH]

Hey guys, I need help in fixing my formula.

Basically I have a table which contains Forecast hours and Spend hours. When the Spend Hours has a value it overrules the Forecast Hours.

I want to calculate the deviation between Forecast en Spend and use that percentage to calculate the total hours spend at the end.

But my result keeps giving a (empty) when I  know that is wrong because I have the data in my table.

I've tried changing the 0 to BLANK() but no result

Test2 = 
var cal = SUMX(FILTER(ALL('FC Uren'),'FC Uren'[Geboekte uren]=0),'FC Uren'[Forecast uren])
var act = SUMX(FILTER(ALL('FC Uren'),'FC Uren'[Geboekte uren]<>0),'FC Uren'[Geboekte uren])
var dev = 1+IF(ISNUMBER(SELECTEDVALUE('FC Uren'[Geboekte uren])),(act-cal)/cal)
return
IF([Totaal geboekte uren]<>0,
dev*SELECTEDVALUE('FC Uren'[Forecast uren]))

[Adamboer]

It seems like the DAX formula is not returning the expected result. One possible reason could be that the conditions used in the formula are not filtering the table correctly. Here's a suggestion to modify the formula and try to solve the issue: Test2 = VAR Cal = SUMX( FILTER( ALL('FC Uren'), 'FC Uren'[Geboekte uren] = BLANK() ), 'FC Uren'[Forecast uren] ) VAR Act = SUMX( FILTER( ALL('FC Uren'), 'FC Uren'[Geboekte uren] <> BLANK() ), 'FC Uren'[Geboekte uren] ) VAR Dev = IF(Cal <> 0, (Act - Cal) / Cal, BLANK()) RETURN IF( Act <> BLANK(), Dev * COALESCE('FC Uren'[Forecast uren], BLANK()), BLANK() ) In this modified formula, the FILTER function is used to return all the rows that have Forecast hours when the Spend hours are empty and vice versa. Additionally, the BLANK() function is used instead of 0 to better handle null or empty values. Finally, the COALESCE function is used to handle cases where the 'FC Uren'[Forecast uren] column has null or empty values. You may need to adjust this formula to match the specifics of your data model, but this should be a good starting point to debug and solve the issue.

[RonaldvdH]

@Adamboer I'm not sure If I was clear enough in my question. So let me try it again and hopefully I can communicate what it is that I want.

Alle the pieces of data you see are in the same table called FC uren

Want I'm trying to do is to calculate the deviating between Geboekte uren en Forecast uren.

In the beginning I make a forecast for how many hours I think i'm gonna need (Forecast uren) and then I start the project and then I get actual hours (Geboekte uren). In the picture below in Column Test3 I want to see the amount of Geboekte uren when there is data but when there isn't any data (starting in 2023-W16) it should do the following.

First we need to calculate the deviation in the weeks when I have values in Geboekte uren.

So for example:

Between 2023-W11 and 2023-W15 I have used (Geboekte uren) 72,75 hours but i've forecasted 75 hours (5x15 hours) so there is a deviation of 3% (75/72,75=1,03)

Starting in 2023-W16 (where there isn't a value for Geboekte uren) I want test3 to display (15 hours + 3% 😃 15,45 hours

[Mahesh0016]

@RonaldvdH  First you open to measure 'FC Uren'[Geboekte uren] and endResult + 0 for E.g sum(Geboekteuren)+0.
similer to [Totaal geboekte uren] this measure, i hope this will help you for correct result.

>>Can you show me a screenshot of the table 'FC Uren' and what's inside the measure [Totaal gebokte uren] and

'FC Uren'[Geboekte uren]?

[RonaldvdH]

@Mahesh0016 here is the screenshot for FC Uren

The formula in the measure  'Totaal geboekte uren' is 

Totaal geboekte uren = CALCULATE(SUM('DWH Uren'[PT Taakuren]),USERELATIONSHIP('FC Uren'[Week+Kenmerk],'DWH Uren'[Week+Kenmerk]))

The calculted column is based on a vertical search from another table

Geboekte uren = VAR __Kenmerk1 = 'FC Uren'[Week+Kenmerk]
VAR __Kenmerk2 = FILTER(ALL('test2'),test2[Weeknummer TD datum+Activiteit] = __Kenmerk1)
RETURN
MAXX(__Kenmerk2,[Aantal geboekte uren])

[RonaldvdH]

@Mahesh0016 

To further clarify my question I've made this screenshot.

For that specific project I think we'll use 15 hours per week but in reality we've used 17, 16.75, 13 and 19,5 hours.

That means, based on the 15 hours I've forecasted I have a deviation

 

[tt_and]

Test2 = 
var cal = SUMX(FILTER(ALL('FC Uren'),'FC Uren'[Geboekte uren]=0),'FC Uren'[Forecast uren])
var act = SUMX(FILTER(ALL('FC Uren'),'FC Uren'[Geboekte uren]<>0),'FC Uren'[Geboekte uren])
var dev = 1+IF((SELECTEDVALUE('FC Uren'[Geboekte uren]*1)>0),(act-cal)/cal,0)
return
IF([Totaal geboekte uren]<>0,
dev*SELECTEDVALUE('FC Uren'[Forecast uren]))

[RonaldvdH]

@tt_and that formula returns an error 

A single value for the column 'geboekte uren'in the tabel FC uren could not be determined

[tt_and]

Oh, sorry, haven't tested it. What about

Test2 = 
var cal = SUMX(FILTER(ALL('FC Uren'),'FC Uren'[Geboekte uren]=0),'FC Uren'[Forecast uren])
var act = SUMX(FILTER(ALL('FC Uren'),'FC Uren'[Geboekte uren]<>0),'FC Uren'[Geboekte uren])
var dev = 1+IF((SELECTEDVALUE('FC Uren'[Geboekte uren])*1)>0,(act-cal)/cal,0)
return
IF([Totaal geboekte uren]<>0,
dev*SELECTEDVALUE('FC Uren'[Forecast uren]))

[RonaldvdH]

@tt_and same result, same error

[tt_and]

Oh, sorry. With max() around selectedvalue it should work?

[RonaldvdH]

I've made the changes you suggested but still I get an error

the function MAX only accepts a columnreference as argument

Test2 = 
var cal = SUMX(FILTER(ALL('FC Uren'),'FC Uren'[Geboekte uren]=0),'FC Uren'[Forecast uren])
var act = SUMX(FILTER(ALL('FC Uren'),'FC Uren'[Geboekte uren]<>0),'FC Uren'[Geboekte uren])
var dev = 1+IF((SELECTEDVALUE('FC Uren'[Geboekte uren])*1)>0,(act-cal)/cal,0)
return
IF([Totaal geboekte uren]<>0,
dev*MAX(SELECTEDVALUE('FC Uren'[Forecast uren])))

[tt_and]

Hi. Can you show me a screenshot of the table 'FC Uren' and what's inside the measure [Totaal gebokte uren]?