Categorizing text values

[Chrisblanc]

Hello everybody, 

I am currently struggling with something that seems very simple; 

I have within my table of data a column called "Title" in with each row contains one value among "Mr", "Ms", "Sir", "Mme", ....(there is many different values because the data is in different languages). My objective is to count all "titles" corresponding to male and all "titles" corresponding to female in order to get a final overview like "40% Men / 60% Women".

Is there anyone that could help me with that ? 

Thank you all ! 

[Anonymous · Accepted solution]

Hi @Chrisblanc ,

I'd like to suggest you create a mapping table for these descriptions.

For example:

Name, Desc
Man,Mr
Man,Sir
Woman,Ms
Woman,Mrs

Then you can use name field of above table as category to create a visual and write a measure to compare with table who stored different descriptions and get the count.

Compare = 
VAR tagged =
    ADDCOLUMNS (
        'Sample',
        "Type", CALCULATE (
            FIRSTNONBLANK ( Mapping[Name], [Name] ),
            FILTER ( ALL ( Mapping ), SEARCH ( Mapping[Desc], 'Sample'[Text], 1, -1 ) > 0 )
        )
    )
RETURN
    COUNTROWS ( FILTER ( tagged, [Type] IN VALUES ( Mapping[Name] ) ) )

Regards,

Xiaoxin Sheng

[HotChilli]

You could add a new column in power query editor. M or F depending on Title values. It's basically going to be a big 'if' statement. Once you have that working, you write measures in DAX to count the m and f rows and work out percentages