7 Replies Latest reply on Sep 18, 2017 2:40 PM by Jim Dehner

# do not affect the avg line due to the category

Hi,

I have a calculated field to calculate the satisfaction of the clients from the results of a survey and it is like that :

Satisfaction :

SUM(IF [Result] = '2' THEN

0.2 * [Number of Records]

ELSEIF  [Result] = '3' THEN

0.5* [Number of Records]

ELSEIF  [Result] = '4' THEN

0.8* [Number of Records]

ELSEIF  [Result] = '5' THEN

1* [Number of Records]

END)

/ SUM([Number of Records])

This is visualized as below changing by a category filter in the sheet :

And I want the avg line to be stable when i change the category on the right. i tried to use LODs but couldn't fix it. as i am new to the tableau I will appreciate all of your help. thanks.

Deniz

• ###### 1. Re: do not affect the avg line due to the category

Good morning

I can't say that I understand your calcualtions but the formula below will give you a fixed line -

{ FIXED

SUM(IF [Result] = 2 THEN

0.2 * [Number of Records]

ELSEIF  [Result] = 3 THEN

0.5* [Number of Records]

ELSEIF  [Result] = 4 THEN

0.8* [Number of Records]

ELSEIF  [Result] = 5 THEN

1* [Number of Records]

END)

/ SUM([Number of Records])

)}

the result looks like this

I just put the formula on a refence line so that it went across the entire viz

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 2. Re: do not affect the avg line due to the category

let's just call the one you suggested the second avg line, I just did and visualzied the two avg lines, the second really didin't change while i change the categroy filter, just an another question, the two avg lines aren't exactly the same, very very close but not the same, do you have an idea?

and i also added a month filter the the viz and the second avg line also didn't change and i want it nit to change only due to the category filter.

thanks again.

Deniz

• ###### 3. Re: do not affect the avg line due to the category

Thanks - My "Second Sverage Line" is not using the "Average " from the analytics - it is using a reference line that I set to the calculated average based on the Fixed calcualtion - I am not certain what your Case statement and "averaging" will do with Null values in both the weighting calcualtion and in the sum records portion -

Jim

1 of 1 people found this helpful
• ###### 4. Re: do not affect the avg line due to the category

Hi again Jim, I got your point, in fact what you advised me served me a lot and i got your point about null values. thus i tried to have a twbx file closest to my real work and i eliminated all the null values. as you can see in the pic below, i have a satisfaction (weighted avg of my own) and the fixed one ({ FIXED : [Satisfaction]}), and i have no problem while i change the category, but when i deselect all in the moths filter, and even choose one month the two avg points are different, in fact i only wanted that my fixed expression would fix only the category but now i couldn't understand what it fixes and how does it calculate this value. perhaps the attached twbx file would say a lot if you have time to look at it.

Thanks a lot.

• ###### 5. Re: do not affect the avg line due to the category

Hi

I am finally back from travelling and took a hard llok at this - the way a fixed LOD expression work is it creates permutaions of all dimensions that appera befor the colon (:) and then aggregates the measures based on the forumla after the colon - so if I understand correctly you want to know know for each month (FIXED) what the Satisfaction is for all categories and be able to vary the category and see how the selected category compares to the monthly total - I mis-undertood before - thought you wanted the fixed total across all months -

so what you want to do is fix at the month level -see below - I recacluated the function

SUM(IF [Result] = '1' THEN 0* [Number of Records]

ELSEIF [Result] = '2' THEN

0.2 * [Number of Records]

ELSEIF  [Result] = '3' THEN

0.5* [Number of Records]

ELSEIF  [Result] = '4' THEN

0.8* [Number of Records]

ELSEIF  [Result] = '5' THEN

1* [Number of Records]

END)

/sum([Number of Records])}

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

1 of 1 people found this helpful
• ###### 6. Re: do not affect the avg line due to the category

Dear Jim, you are great, and your explanation is very helpful, thanks a lot!!!

• ###### 7. Re: do not affect the avg line due to the category

you are welcome