7 Replies Latest reply on Apr 20, 2017 1:13 PM by Nathan Flanagan

# LOD with If statement

I have tried to create this LOD calculated field.

IF  ISNULL([Clear Dt])

THEN

{ FIXED   [ID]: AVG([Total Cost])}

END

I would assume this to take the average of each [ID] and then if I sum the dimension when I display the field, it should return 17 thousand and something. Instead, I see that it is summing each of the values of the [total cost] without first finding the average. I don't know why this doesn't work and would love any help or suggestions.

Here is a packaged workbook as well. I hope it works, this is the first time posting a question.

Thanks, Nathan Flanagan

• ###### 1. Re: LOD with If statement

not so far off:

{ FIXED   [ID]: avg(IF  ISNULL([Clear Dt]) then [Total Cost] end ) }

2 of 2 people found this helpful
• ###### 2. Re: LOD with If statement

Hi Nathan,

Your calculation is Correct. Just Change your SUM to Avg in the Text Label. Right Click>Measure>AVerage.

Thanks

Deepak

• ###### 3. Re: LOD with If statement

@Deepak Rai, if he does this and then pulls Id off the shelf, the result is 6648, which I do not believe is what he's after.

• ###### 4. Re: LOD with If statement

Hi Justin,

In his LOD , his intention is to FIX it to ID, So pulling off IDs from Rows doesn't come into Picture. That would be a different scenario.

Thanks

Deepak

{ FIXED   [ID]

• ###### 5. Re: LOD with If statement

Thanks for the help!

Okay, I don't think I explained my self well enough.

So, we have more one with multiple id's with on price. And we have another Id with a different price. The price will repeat for the same id. There fore I want to find the average of each id. Then I want to find the sum of those.

Since there is one Id with an average of 11 thousand. And an Id with 8 rows of 6 thousand each. I want it to return one one row of 6 thousand and one row of 11. So when I sum it up it will equal 11 thousand.

Does that make sense?

--

Nathan Flanagan

• ###### 6. Re: LOD with If statement

That's how I understood your original post. See the alteration in my original reply. Should do the trick.

• ###### 7. Re: LOD with If statement

Justin,

That worked perfectly!

Thanks so much,

-Nathan Flanagan