cancel
Showing results for
Did you mean:
RonaldvdH Helper III

## 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])
)
)
)
1 ACCEPTED SOLUTION

Accepted Solutions Community Support

## Re: Need help with my formula

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
8 REPLIES 8
santiagomur Resolver I

## Re: Need help with my formula

```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 Helper III

## Re: Need help with my formula

@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 Resolver I

## Re: Need help with my formula

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)`
josemanuelcayon Helper I

## Re: Need help with my formula

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

## Re: Need help with my formula

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

Community Support Team _ Xiaoxin Sheng
If this post helps, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
RonaldvdH Helper III

## Re: Need help with my formula

@santiagomur  shouldn't the last measure be an 'average' instead of 'sum' ?

`total LVC= sum(Gemiddelde productiviteit LvC)`

total LVC=average(gemiddelde productiviteit LvC)

The sum just returns the total value of the entire colum but i need the average. Obviously ive changed the formula to average and now i get a plausible value but in the answer by @josemanuelcayon the result is different

In the formula @santiagomur the average is 1,04 and the formula by @josemanuelcayon  the average is 1,44

What is the difference between both formula's ?

josemanuelcayon Helper I

## Re: Need help with my formula

I don't know exactly what the difference is, so I recommend you to take a sample, do the calculus and check with the result got by both expressions, then you will able to know which one is the correct.

RonaldvdH Helper III

## Re: Need help with my formula

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

Announcements #### Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section. #### April 2020 Community Highlights

Info on our Super Users, MBAS content and badges, and updates to our support articles. - Read the full Community Highlights. Top Solution Authors
Top Kudoed Authors
Users online (2,059)