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

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

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

    tt_and same result, same error

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

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

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

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