Forum Discussion
RonaldvdH
Post Patron
Post PatronWhat's wrong with my formula
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 ca...
RonaldvdHPost Patron
Post PatronMahesh0016 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])
tt_and
Advocate I
Advocate I
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]))