tableau-apiaggregationtableau-desktoplevel-of-detail

Count occurence based on another column and return label having maximum count for each row

I have two categorical columns "Job Industry Categories" and "Wealth Segment" and I can create a crosstab using "Job Industry Categories" in rows and "Wealth Segment" to count values for each industry.

This is how my crosstab looks like currently:

Pic 0

But I want to dynamically count wealth segment column for each industry and then return the wealth segment with maximum count for each industry.

This is what I want to acheive:

Pic 1

What I have tried:

I have tried using LOD expression to get the max of count of wealth segment for each industry but that returns a non-aggregated value and I am not able to extract the wealth segment label for the corresponding value.

MAX(
{ FIXED [Job Industry Category], [Wealth Segment] : COUNT([Wealth Segment]) }
)

I have also tried using IF THEN statement with LOD Expression but, since LOD expression is a non-aggregated value, it throws error. This is what I tried:

IF 
{ FIXED [Job Industry Category], [Wealth Segment] : COUNT([Wealth Segment]) } =  MAX(
{ FIXED [Job Industry Category], [Wealth Segment] : COUNT([Wealth Segment]) })
THEN
[Wealth Segment]
END

ERROR: cannot mix aggregate and non-aggregate with this function

Thanks in advance.

Solution

  • You are doing 2-3 things not in the correct way here.

    1 Your first screenshot (table) is not your data, but already a crosstab query (called pivot table in excel) so how can you imagine some extra column either in row or in column. (second screenshot). tableau is not a spreadsheet like excel, where you can create rows/columns as per your convenience.

    2 Why have you used count(wealth status) as aggregation. Though it will give same values but here you required count of records/data called as number of records in tableau's earlier versions.

    3 You have not considered the situation where both counts are equal. (these can very well be equal, can't they?)

    If you want you can create a new column altogether (without a cross tab view/viz of course) where you can get name of wealth category having more records than other.

    I created some random data to replicate your problem. My data looks like (there are 100 rows) (table name is weal.csv)

    enter image description here

    Cross-tab looks like

    enter image description here

    Create a calculated field say Max of Wealth with the following calculation

    IF {Fixed [Sector], [Wealth Status]: COUNT([weal.csv])} =
{FIXED [Sector]: MAX({Fixed [Sector], [Wealth Status]: COUNT([weal.csv])})}
then [Wealth Status] END

    You can filter out null values from this field and can get desired viz as

    enter image description here

    Note two results in health sector

    My sincere advice to new tableau users - unlearn spreadsheet first

    Good luck