7 Replies Latest reply on Apr 27, 2018 8:24 AM by Jyothisree Rayagiri

Finding the maximum value from all newspapers in a country

I am a newbie to tableau and I have found Tableau interesting so far. However I do get stuck up in between and this is one of such moments.

Problem -

I have data of all female news reporters belonging to different countries. What I am trying to find is - Which newspaper employs the maximum females for every country.

What I have done so far -

Having Country, Newspaper, Gender(filtered for females), Count(Newspaper) , I get all newspapers with count of females in every country. But I am unable to get only the maximum and exclude the remaining rows.

• 1. Re: Finding the maximum value from all newspapers in a country

We will be able to help if you can share your workbook with sample data.

You can use rank() function/ or use the rank table calculation on the count measure

-- to rank the newspapers for each country based on the count of females.

Apply a filter to show only Rank 1  and you will see only the top newspaper for each country

• 2. Re: Finding the maximum value from all newspapers in a country

Hi Naveena Benjamin ,

It seems you have already calculated the female count for each newspaper country wise and you are trying to get the max count in each country .if this what you are looking for please use filter on newspaper ,go to Top  -> select By field -> Top1 -> female employes -> Count.

But you need to create hierarchy & put country on top level ,then newspaper and then employees (filter female).

Without creating hierarchy it will return only return the newspaper with max female employees.

• Other thing that you can do to achieve this is use LOD .

{fixed [country],[newspaper] : count([employees])\countd([employees]) }

and use filter accordingly . Please check the use of count or countd depending on complexity of data you got .

Try doing it ,in case you stuck somewhere please share your twbx file .

-Nikher Verma

• 3. Re: Finding the maximum value from all newspapers in a country

You got my vote , Naveena Benjamin always try to share twbx file .

-Nikher Verma

• 4. Re: Finding the maximum value from all newspapers in a country

I have attached my twbx file. Thanks

• 5. Re: Finding the maximum value from all newspapers in a country

Hi Naveena

Please find the workbook attached with the solution

Create a calculation

{ fixed [Country],[Newspaper]

: sum ( if [Gender]='Female' then 1

else 0 end )}

Drop this to your text label and setup a table calculation - rank over Newspaper.

And filter on the final rank measure to show only rank 1 items

You may need to extend your calculation  if [Gender]='female' or [Gender]='f'   if you want to include records marked f also to the count.

J

1 of 1 people found this helpful
• 6. Re: Finding the maximum value from all newspapers in a country

Thanks so much. Could you please explain why that had to be done? Why the sum and the ranking?

• 7. Re: Finding the maximum value from all newspapers in a country

The calculation is a level of detail calculation.

You can build aggregations which are fixed on the dimensions.

We are calculating the total number of rows for each Country and Newspaper.

If you want just the total for newspaper irrespective of the country , it will be fixed [Newspaper]

This sum will hold even if you have more dimensions on the view.

Once I have this total of female employees, I have ranked the newspapers based on the total.

Since you want to show the ones with top number of employees, we are filtering to rank=1

If you want to show top 2 for each country, you can filter rank 1 to 2.