Afternoon all of you in Tableau-land!
Ok so I can't figure this out so I am hoping one of you can and please tell me how you did it so I can learn from it.
Here's a bit about my situation:
I am building a dashboard to manage a fleet of vehicles. The data that is in the attached workbook is a sample of my data without the costs. You'll notice that each vehicle is assigned to an organization and gives typical things such as make, model, and monthly mileage. For the costs - which isn't really my current issue and I only mention it as I am wondering if it's part of my current issue and I don't realize it because it affect the shape of the data. That said, each monthly cost is assigned a "sales code" This helps the accounting types itemize what the costs are for. The majority of time a vehicle has 2-3 sales codes and the sum of these amounts = monthly costs.
Now that we have that out of the way on to my real issue:
What I have been trying to do is to figure up utilization to help managers be more efficient. The way we are calculating that is to make a comparison of the sum of the monthly mileage to the average 6 month mileage for a vehicle. If the sum of the monthly mileage is higher than the average 6 month mileage for a vehicle then this situation should be noted.
The viz that I am looking for is a KPI = meaning that I just want to show one number or the number of under utilized vehicles for the max month of the record set.
Here is my logic:
Step 1: Figure up the Max Month of the record set: Max Month= {FIXED [Vehicle Tag]:MAX([Transaction Date])}
Step 2: Figure up the difference between the max month and the months in the record set so that I can keep just the last 6 months of the record set: Date Month = DATEDIFF('month',[Max Month],[Transaction Date])
Step 3: Create a calculation that will sum the monthly mileage that is not excluded from the view based upon the previous step: Last 6 Months Avg Mileage = If [Date Month Diff]>-6 THEN [Monthly Milage] END
Step 4: Change the aggregation of [Last 6 Months Avg Mileage] from SUM to AVG.
Sheet 1 is the result of my work up till step 4:
Based upon some calculations that I did in Excel I am getting the correct summation but not the correct averages:
So that is where I need some help! - Please!
Thank you,
Scott
PS - I am using version 2018.1
Hi Scott,
I think your issue might be in your last step - changing the aggregation to AVG. What Tableau does to produce the AVG() aggregation is SUM the measure, and then divide it by SUM(Number of Records) in the view. Because of your Sales Code field, each vehicle has more than 1 record:
Your [Last 6 Months Avg Mileage] calc should look like this instead:
If this answer resolved your issue, please mark it as the correct answer!
-Christina
