How to create a new custom category which has mutual data items?

[KarunaTiwari]

Hi All,

I have a Category field GEO which has values like India, USA and UK.

Now I want to show the revenues of India vs Overall in a bar chart.

I am able to create custom category -India(India) and Others (UK, USA).

But I want to create custom categories - India(India) and Overall (India, UK, USA).

How can I achieve that?

Thanks in advance!!

[collinelliot]

I would look into a multilabel format. That might do what you want. 

Here's an example that might get you started:

http://blogs.sas.com/content/sgf/2016/12/16/creating-and-using-multilabel-formats/

[collinelliot]

I actually have no idea if these are applicable in Visual Analytics. I should have read the topic area of your post more carefully.

[itchyeyeballs]

One way would be to create calculated fields that can be agregated, something like:

IF ( 'GEO'n = 'INDIA' )
RETURN {1}
ELSE {0}

IF ( 'GEO'n In ('INDIA', 'US','UK') )
RETURN {1}
ELSE {0}

Then just use those calculated fields in the chart

[KarunaTiwari]

@itchyeyeballs Thanks for your reply.

I tried creating calculated fields as suggested by you but that did not work.

I want a bar chart like below:

[itchyeyeballs]

Can you post details of what you tried and how it went wrong?

I just kncked this one up

 

[KarunaTiwari]

Hi @itchyeyeballs,

I have two columns in my data GEO and Business Verticals.

The GEO field contains - India, UK, USA, etc

I want to create a barchart showing the Distribution of Business verticals in India vs all the countries(including India). 

I tried to create two calculated items - Geo_India which is equal to 1 when GEO = India; otherwise 0

                                                             Geo_Overall which is equal to 1 when GEO in (India, UK, USA); otherwise 1

then was trying to convert these 2 measures to category to be used in the bar chart.

but did not get what i wanted to achieve.

Other option that I tried earlier was to create a custom category with the help of GEO field.

But then I am not able to put India under both the labels - India and Overall.

Thanks for your help.

[itchyeyeballs]

Put your two calculated fields into the measures section of the visual

[KarunaTiwari]

@itchyeyeballs

If I put my two calculated fields into the measures section of the visual, then it will show me the frequency of these two calculate fields but I want to show the frequency of Business Verticals based on these calculated fields (Number of Business Verticals in India vs number of Business vertiacls Overall)

Basically I want to use them as categories.

[itchyeyeballs]

Does the business verticals column hold numbers that you want to sum?

If you you could change you calculated fields to

IF ( 'GEO'n = 'INDIA' )
RETURN 'Business Verticals column'n
ELSE {0}

[KarunaTiwari]

@itchyeyeballs

No Business Verticals is a categorical field and I want to show frequency of Business Verticals based on GEO.

[itchyeyeballs]

If you put the business verticals category into the category role, then the two calculated fields as measures it should give a chart with India vs Total for each bsuiness vertical.

If thats not what you need can you share some dummy data?

[KarunaTiwari]

@itchyeyeballs Thanks a ton for your time.

Please find attached the dummy data and the graph (Both in Excel).

[KarunaTiwari]

@collinelliot No Problem.. Thanks for you reply.

[itchyeyeballs · Accepted solution]

Like this?

Formula for india is 

IF ( 'GEO'n = 'INDIA' )
RETURN 1
ELSE 0

For overall I just used the default frequency measure but renamed it