Count records as proportion of total in Tableau

0
votes

I have data with sales, details including gender, location, date, etc. There's one row for each sale, so the total number of sales is a count of the rows.

customerid  sale_date   regionid  studentid  sex
18761372    01/09/2016  AFB07458  C2F815C6   1
18761372    01/09/2016  AFB07459  206AA234   0
07189635    01/09/2016  AFB07460  F218C8F1   1
07189635    01/09/2016  AFB07461  F021CD27   0
07189635    01/09/2016  AFB07462  E6145555   1

I'm trying to produce a line graph that shows number of sales by month, split by male and female. However male sales are ~5million for the year, and female are ~13 million. So it's hard to compare whether there's any difference in how the sales vary by month (i.e. whether males are proportionately more likely to buy in september).

I manage to get this to work by creating the following calculated field:

If [sex] = 0
THEN 1/5000000
ELSE 1/13000000
END

This does what I need, but it's not a great solution as it involves me manually checking what each total is and writing it down (admittedly not much of an issue with sex, but if I'm doing it on something with multiple fields then it would become very time consuming.

Is there a way of doing this more elegantly? I tried to use sums and case statements in there but I got "cannot mix aggregate and non-aggregate arguments".

2
tableau-api
Could you please provide a sample data set. – lampbob
Based on your description, I would approach this with a calculated field that shows a percent of total, by month. That way, instead of looking at a count of orders where one group always appears greater, you are looking at their share which will be more uniform. There is likely a workaround for your "cannot mix aggregate and non-aggregate arguments" issue too, if you are able to share a sample workbook we can take a look. – Ben P
@BenP I just tried that and managed to get it working. If you add it as an answer I will accept it. – Moogle

2 Answers

1
votes

I would approach this with a calculated field that shows a percent of total, by month.

Right click on your pill, select 'Quick Table Calculation' and choose 'Percent of Total'.

That way, instead of looking at a count of orders where one group always appears greater, you are looking at their share which will be more uniform.

0
votes

Maybe I'm understanding this wrong but it seems fairly straight forward. Using the sample data provided I just:

  1. Add the date field pill "sale date" to the column shelf(set it to pull back the month)
  2. Drag the "number of records" to the rows shelf(automatically sum the results)
  3. In the "Show Me" section in the top right select line chart.
  4. Drag the sex pill onto the color card, this will break the line into to two, one for male the other for female(you may have to convert the sex pill to a dimension, to do this right click the pill and select "convert to dimension")

Let me know if this solves it for you?