I cannot figure out how to make a stacked bar chart which outlines the average cost of a set of jobs on a monthly basis, and how those costs are divided between different cost categories, in such a way that when a type of job is filtered out, the average costs adjust accordingly.
The relevant features are:
- event_id: Each job has a unique code to identify it, and each event has several costs associated to it
- event_type: Each job falls into one of a handful of categories
- cost_amount: how much an item costs as a part of the job
- cost_subcode: the category each cost is attributed to
- month_date: the month in which the job occured in
Each job has multiple costs of different subcodes assigned to it. Sometimes multiple instances of the same subcode if the costs were incurred on different days. Because of this, simply using
avg[cost_amount]
did not work.
The first aggregation I tried was sum([cost_amount]) / uniqueCount([event_id])
This worked without any coloring, but when I colored the bar chart by cost_subcode because the unique count of event_ids was different for each cost_subcode, as some codes do not appear for every event.
I next tried creating a calculated column "Events Per Month" which held a count of unique event_ids over each month, and using this in the denominator: Sum([cost_amount]) / Max([Events Per Month]).
This worked better as the bar chart now had the correct values when colored, and showed the each cost_subcode's correct proportional spend: (Image of bar chart)
But because the calculated column does not change from filtering: when I filter out certain event_types, the sum of costs decreases, but the number of jobs does not, and the chart shows numbers much lower than what it should (Image of bar chart with filtering)
Is there any way to get a UniqueCount of jobs for each month that ignores the coloring, but can still have filters applied? Or some other workaround.