5 Replies Latest reply on Aug 6, 2018 10:08 PM by Tushar More

# Analysing ONLY rows with certain values, under a particular dimension?

I have a data source with the following columns:

Gender, Name, Favourite Sport

I want to calculate the different sums of people who like particular sports, but only for a certain gender in a particular instance, through a parameter control for users to interact with.

How do I filter the rows based on gender first, then proceed to count the number of people who favour a particular sport? I tried to create a parameter with Male/Female as the values and then building a calculated field:

Case [Gender]

when 'Male' then ...

when 'Female' then ...

but I have no inkling on the functions that I should use. Is it lookup?

I am hoping for my end result on the Worksheet/Dashboard to look something like this:

Table 1: Favourite Sport of Males

SportCount
Soccer3
Tennis 5
Rugby3

And then a separate "Table 2" for Females when I toggle the gender parameter accordingly.

Thanks in advance for the help!

• ###### 1. Re: Analysing ONLY rows with certain values, under a particular dimension?

Hi Jinija,

It will be helpful if you could have attached sample data/workbook.

Anyhow i tried with some sample data and came with below logic.

1. Female =  if ([Gender])="Female" then 1 END

2. Male = if ([Gender])="Male" then 1 END

3. Create string parameter with list of values:

Select Gender

"Female"

"Male"

4. COUNT

if [Select Gender]="Female" then [Female]

ELSE [Male]

END

5. Drag COUNT as measure.

1 of 1 people found this helpful
• ###### 2. Re: Analysing ONLY rows with certain values, under a particular dimension?

Hi,

Create a parameter to select a gender.

Calculation to filter data.

Put this to filter and select True.

Enable parameter control for interactivity. Right click the parameter > Show Parameter Ctrl

Apply count distinct aggregation on person name.

Let me know that helps or not

~Tushar

1 of 1 people found this helpful
• ###### 3. Re: Analysing ONLY rows with certain values, under a particular dimension?

(Note: this example does not use parameters, in case that's still a requirement.)

Assuming the Gender column has a very small library of possible values (e.g. "F" and "M"), you could simply perform the COUNT() aggregation on Gender and place that onto the view.

Next, you can add a filter on Gender and disable the "Show "All" Value" to only have a selection between female or male.

Next, change the filter type to "Single Value (list)".

Your filter will now look like this:

Just to match your presentation example on your original post, hide the CNT(Gender) pill in the sheet.

Finally, you could also drag the CNT(Gender) pill to Text on the Marks shelf.

You will now have the number of people with their favorite sport filtered on gender.

Kind regards,

Jan

2 of 2 people found this helpful
• ###### 4. Re: Analysing ONLY rows with certain values, under a particular dimension?

Thanks everyone for your kind replies!

I was wondering if any additional step was needed if I wanted to keep Female/Male parameter but my Gender column is filled with just F and M only?