Hey, Kerry! Welcome to Tableau.
I think I understand what you are dealing with- but if you could take a snapshot of how the data is currently formatted vs. how you would like it to be formatted that may help us, help you!
It sounds like one of your tables is formatted correctly, while the other is not. Is that correct?
Thanks for your response. The data is all in the same table. The way that the table has been structure is that they have added the metric names in a separate table, which is referenced with a key. This brings back the metric name (the measure in the screen shot below).
I can't show the data, because it is sensitive, but these are the measures:
I have created a calculated field for All Emergency Admissions, which is simply the sum of the bottom 4 measures.
Above those is ae_attendances (essentially the same as my calculated field for All Emergency Admissions) which has a 01, 02 and 03 in the "department" column, which shows which "type" it is, as seen at the end of the bottom 4 measures.
Because these are blank in the "department" column, if I use a filter, it will never show both measures on the same graph.
Does this help to explain it? I'm struggling
This is the data table (well, a small selection of the columns) that column with "Type 1" is the department column.
This is the Emergency Admissions section:
It all depends what is your true data structure
if you have something like that:
then you can try to create calculated field MetricASum as
and then divide it by max(metricB)
if you have
then I would do:
if [measure] ='metricA_dept01' then sum([value]) end
[Adept1] / sum(metricB_dept01)
for metricb you need to choose whether sum(), Min(), max() would be the best at it depends on data.
you can create proper field for adept2 and adept3, sum it and then divide by sum of bdept1,2 and 3 to have total shares.
That won't work with this data.
I need to try to create a metricB_dept01, metricB_dept02 and metricB_dept03.
So I sort of need something like this:
if [department] = '01'
then sum those values to create measure metricB_dept01
ok and what values you expect in department_type for emergency admission type 1 and emergency admission other?
so it's gonna be something like:
calculated field newDeparmentType:
if isnull([department_type])=false and [metric_calculation] = "AE_Attendances" then [department_type]
elseif isnull([department_type])=true and [metric_calculation] = "Emergency_admissions_other" then "Type Other"
elseif isnull([department_type])=true and [metric_calculation] = "Emergency_admissions_type1" then "Type 1"