Forum Discussion

RonaldvdH's avatar
RonaldvdH
Icon for Post Patron rankPost Patron
7 years ago
Solved

Need help with my formula

Guys, i need some help with my formula because i think im almost there but it still returns an error

Basically i want a measure that calculates that IF [Aantal dgn] <> BLANK then it devides 2 values.

 

Gemiddelde productiviteit LvC =
Divide(
            IF(
                Filter('V&P Civiel LvC';'V&P Civiel LvC'[Aantal dgn]<>BLANK());(
                          SUM('V&P Civiel LvC'[Prognose duur]) / SUM('V&P Civiel LvC'[Aantal dgn])
          )
     )
)
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi RonaldvdH ,

    You can try to use below formula if it suitable for your requirement:

    Gemiddelde productiviteit LvC =
VAR div1 =
    CALCULATE ( SUM ( Table[Prognose duur] ); Table[Aantal dgn] <> BLANK () )
VAR div2 =
    CALCULATE ( SUM ( Table[Aantal dgn] ); Table[Aantal dgn] <> BLANK () )
RETURN
    IF ( div1 > 0 && div2 > 0; DIVIDE ( div1; div2 ) )

    If above not help, can you please share some sample data for test?

    Regards,

    Xiaoxin Sheng

8 Replies

  • santiagomur's avatar
    santiagomur
    Icon for Resolver II rankResolver II
    7 years ago
    emiddelde productiviteit LvC =

            IF(
[Aantal dgn]<>BLANK();
                          SUM('V&P Civiel LvC'[Prognose duur]) / SUM('V&P Civiel LvC'[Aantal dgn]);"were else"
          )

     and use as new column

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

      santiagomur i need this to be a measure is that possible ?

       

      Your formula returns an error by the way, the error is roughtly translated into 'the column '[Aantal dgn] cannot be determined, it either does not exist o there is no current row for that column

       

      Both arguments are not true because there is a column and it HAS values.

       

      • santiagomur's avatar
        santiagomur
        Icon for Resolver II rankResolver II
        7 years ago

        i don't know the exactly name of your columns so you need to change, but follow the same arguments. 

        if you need measure what i would do first is create the column and then create the  measure you need. 

         

        you are trying to validate row by row and as far i know it cant be done with a measure.

         

        Gemiddelde productiviteit LvC =

            IF(
[Aantal dgn]<>BLANK();
SUM('V&P Civiel LvC'[Prognose duur]) / SUM('V&P Civiel LvC'[Aantal dgn]);
        "were if false if Aantal dgn = Blank"
          )

         then a measure

        total LVC= sum(Gemiddelde productiviteit LvC)
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi RonaldvdH 

     

    If what you want to do is to avoid the error of dividing by 0, just use this formula:

     

    Gemiddelde productiviteit LvC = Divide( SUM('V&P Civiel LvC'[Prognose duur]), SUM('V&P Civiel LvC'[Aantal dgn]), 0 )
     
    anyway, if you want ot filter the blanks:
     
    Gemiddelde productiviteit LvC =
    IF( NOT( ISBLANK( SUM('V&P Civiel LvC'[Aantal dgn]) ) ), 
        DIVIDE( SUM('V&P Civiel LvC'[Prognose duur]), SUM('V&P Civiel LvC'[Aantal dgn])
              )
     )
     
  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi RonaldvdH ,

    You can try to use below formula if it suitable for your requirement:

    Gemiddelde productiviteit LvC =
VAR div1 =
    CALCULATE ( SUM ( Table[Prognose duur] ); Table[Aantal dgn] <> BLANK () )
VAR div2 =
    CALCULATE ( SUM ( Table[Aantal dgn] ); Table[Aantal dgn] <> BLANK () )
RETURN
    IF ( div1 > 0 && div2 > 0; DIVIDE ( div1; div2 ) )

    If above not help, can you please share some sample data for test?

    Regards,

    Xiaoxin Sheng

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

      The correct answer was 1,04 so that solved my problem, thanks guys for all your help