5 Replies Latest reply on Nov 26, 2013 7:08 AM by Djalal Bougriou

# Unable to change a measure(if statement) to a dimension even though it is a dimension in the first place

Hi everyone,

I contact about a calculated field I want to create. I want to create a calculated field named Income repartition.

The formula I used is the following

If sum([Gross Income])<5 then '<5€'

Elseif sum([Gross Income])=5 then '5€'

Elseif sum([Gross Income])<10 then '5€-10€'

Elseif sum([Gross Income])=10 then '10€'

Elseif sum([Gross Income])<20 then '10€-20€'

Elseif sum([Gross Income]))=20 then '20€'

Elseif sum([Gross Income]))<30 then '20€-30€'

Elseif sum([Gross Income]))=30 then '30€'

Elseif sum([Gross Income]))<40 then '30€-40€'

Elseif sum([Gross Income]))=40 then '40€'

Elseif sum([Gross Income]))<50 then '40€-50€'

Elseif sum([Gross Income]))=50 then '50€'

Else '>50€'

End

In the end this is valid, but the field created is considered as a measure in Tableau & I am unable to turn it into a dimension how it should be. When I use the formula by removing the sum(), the field created is considered as a dimension. (But this field is not the one that needs to be created.) The other formula is the following.

If [Gross Income ]<5 then '<5€'

Elseif [Gross Income ]=5 then '5€'

Elseif [Gross Income ]<10 then '5€-10€'

Elseif [Gross Income ]=10 then '10€'

Elseif [Gross Income ]<20 then '10€-20€'

Elseif [Gross Income ]=20 then '20€'

Elseif [Gross Income ]<30 then '20€-30€'

Elseif [Gross Income ]=30 then '30€'

Elseif [Gross Income ]<40 then '30€-40€'

Elseif [Gross Income ]=40 then '40€'

Elseif [Gross Income ]<50 then '40€-50€'

Elseif [Gross Income ]=50 then '50€'

Else '>50€'

End

My question is then, is this a known issue that when using this type of formula it is considered as a measure, even though it should be considered as dimension? If so, would you what to do to have it as a measure?

I have enclosed a packaged workbook, where both formulas are present, they are named "Income repartiton" & the other one is named sum Gross Income Repartition.

I hope my question is clear enough & I provided enough information to answer.

Best regards,

Djalal

• ###### 1. Re: Unable to change a measure(if statement) to a dimension even though it is a dimension in the first place

Djalal, your question is very well asked except that you didn't indicate what you are ultimately trying to accomplish. When I opened your workbook and used your calc to build a viz I get this: What are you wanting to end up with?  FYI: The 'issue' you described is 'as designed' behavior.

--Shawn

1 of 1 people found this helpful
• ###### 2. Re: Unable to change a measure(if statement) to a dimension even though it is a dimension in the first place

Hi Shwan,

Thanks a lot for your answer! Let me explain the view I want to construct, for each range of amount, I want to assign the corresponding number of donors to the corresponding range ("sum Gross Income repartition"), as you can see in the image below. However, the field "Sum Gross Income repartition" is a measure I cannot turn into a dimension. Even though the field named "Income repartition" works as a dimension & basically has the same formula. I enclosed a new workbook in which I added the field "Number of donors". Sheet 3 is actually the result I want to accomplish but with the field "Sum Gross Income repartition."

Hoping I'm clear enough.

Djalal

• ###### 3. Re: Re: Unable to change a measure(if statement) to a dimension even though it is a dimension in the first place

Let me know if the attached gives you what you need.  Since we cannot use the Aggregated field as a dimension, we can use a table calc with "Ignore in Table Calculations" disabled on the discrete Sum Gross Income repartition pill to generate the # of Donors per aggregate level.  The calculated field IF FIRST()==0 THEN SIZE() END ensures only one mark per row is returned, and turning off "Stack Marks" in the Analysis menu eliminates unneeded space in the sheet.  Also, note the ind_id field has to be on the Level of Detail as a dimension for this to work.

I hope I have used this technique correctly, as it still confuses me a bit.  I agree it would be nice to be able to use aggregates as dimensions in situations like these and don't really understand why we cannot.

I believe this is what you are after; I did try to double check the #s to make sure the values being returned were accurate, but let me know if you think it is incorrect.

• ###### 4. Re: Unable to change a measure(if statement) to a dimension even though it is a dimension in the first place

Sorry Djalal I took a look at your new workbook and I am still unclear what you are trying to accomplish. In your screenshot you have 10E having 1494 donors, where does that number come from? Where does that screenshot come from?

As far as what is a Dimension and what is a Measure I think you are a bit confused. Most things can be changed from a dimension to a measure and vica-versa. But somethings can NOT. Aggregated measures can NOT be turned into a Dimensions. Your 'Income repartitions' can be a measure because the [Gross Income] field in not aggregated. When you use an aggregation for that field, SUM([Gross Income]), then it will get turned into a Measure. This is by design.

You can take your Income repartition field and drag it to the Measures shelf and Tableau will aggregate it as COUNTD...

[Just saw Matthew's post come through so I'll end here...]

--Shawn

1 of 1 people found this helpful
• ###### 5. Re: Unable to change a measure(if statement) to a dimension even though it is a dimension in the first place

This is exactly what I was looking for Matthew, thanks a lot! Thank you both for your answers & rapidity.

Sorry, Shawn I may have been unclear in what I had asked, I wanted to use the field SUM([Gross Income]) to work as a dimension, but Matthew's post solved it.

Have a good evening or day depending where you are!

Djalal