Forum Discussion

RonaldvdH's avatar
RonaldvdH
Icon for Post Patron rankPost Patron
3 years ago

What'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...
Need Help
Mahesh0016's avatar
Mahesh0016
Icon for Super User rankSuper User
3 years ago

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's avatar
    RonaldvdH
    Icon for Post Patron rankPost Patron
    3 years ago

    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])

     

     

    • tt_and's avatar
      tt_and
      Icon for Advocate I rankAdvocate I
      3 years ago

       

      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's avatar
        RonaldvdH
        Icon for Post Patron rankPost Patron
        3 years ago

        tt_and that formula returns an error 

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

    • RonaldvdH's avatar
      RonaldvdH
      Icon for Post Patron rankPost Patron
      3 years ago

      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