Here's the Tableau Public workbook link where I need assistance: Here
I'm trying to learn LoD expressions - How it works, and the logic behind the scenes. To understand this better, I was referring to a YouTube video by sqlbelle - and video link is here (This is just for context - you might need this information to help me)
My problem:
I originally created the LoD expressions (Saved by the name of "Problem LoD expression") with the intent of calculating Average Sales Per Customer - for each state. I understand INCLUDE would've been the correct LoD expression - but by using FIXED LoD I have gotten other questions/concerns, which just seem fundamental and easy to encounter again if concepts is not clear.
The problem:
As you'd see under the "Main Sheet" I've added "Problem LoD Expression" in the tooltip and kept it as a SUM aggregate - To understand further, we will focus on the state of Illinois
As you'd see by hovering over it, it would say the total Sales is : 715, 936 - but that's incorrect.
The correct amount of sales is 80, 166.
Where does the huge difference come?
That's explained under "Sheet 1"
It would appear that my LoD expression is indeed working - It has given Total Sales for each customer ID - but when bringing in State to the level of detail, it's not filtering only the Sales Number done in the state - The customer IDs that have been picked up are people who have done sales in state, yes, but the value of sales that is displayed is the Total Sales done by them, anywhere.
A good example of this is the customer: AB-10105 - If you were to filter for Illinois & Arizona, this customer would appear in both of them - and have the same exact same sales value.
And thus, this inflates the sales number.
Question 1 - Why is this happening? What's the logic in Tableau that's saying "Pick up the customer even if one of their sales in the respective state for which you're viewing"
Second Problem Statement:
Now if I look at the data behind this calculation (Sheet 3)- say for example - looking at the calculation for how 715, 936 comes - and move it to Google Sheets. This number does not come up when i SUM up the Problem LoD Expression column that comes up - So...How is 715, 936 even coming?
If the SUM is wrong - How is the average even coming? (3021 is the computed average)
I'm really confused here - and need help.
Have just started learning Tableau - and this is has become a bottleneck.
Apologies if my question seems basic - It's really been just a month since I started learning.
Workbook is attached too.
Hi,
It's terrific that you're learning about LOD's. They're very powerful calculations/functions and they've helped tremendously in not having to use Table Calculations.
Your Problem LoD Expression is calculating correctly: {FIXED [Customer ID]: SUM([Sales])} Which essentially says, focus only on Customer ID and return back only the SUM of that particular customers sales, regardless of State.
Which is why you see what you believe to be a duplication of numbers when you put State into the view and select a couple of States. Tableau really isn't providing a duplicated result. It's providing the AGGREGATE displayed amount irrespective of anything else.
And, in some cases, some businesses want to see that number in that manner. They need that aggregated number despite the level of detail in the view. See the example below...
Also, that number, when viewed by State, isn't really inflating anything...you'll know that it's an LOD when you see that number for both States and it also appears in the GRAND TOTALS for Row:
So, when constructing the syntax of an LOD, you'll want to consider the level of detail that you're going to view the data in the worksheet.
At the Customer ID level, which is fairly high in this case, it will be the aggregation at that level. If you're going to add State, then you're going to a different level of detail and you'll want to include State in the syntax so that your LOD looks like the following: {FIXED [Customer ID], [State] : SUM([Sales])} Which then returns the 80,166 number that you're looking for, by Customer ID and by State, filtered to a single State:
Another method of getting to that number without having State in the calculation syntax, but having State in the Filters card, is to right-click State in the Filter Card and then select Add-to-Context from the sub-menu options, which forces Tableau to filter to the State level ahead of other operations in the Order of Operations.
By Customer ID, by State, a lower level of detail:
By Customer ID, by Order Date, Product Name, State...note that in the view, with the above syntax not being changed to include State, you can display both the Total Sales by Customer ID with your LOD and the line level of detail for a particular sales item for that same Customer ID...to be able to compare sales at a different level of detail in the same view.
In this case it's Customer ID sales (regardless of State, using just {FIXED [Customer ID] : SUM([Sales])} ) versus a particular order at the State level...
Or just at State Level and then lower levels of detail...for comparative purposes, depending on which DIMENSION you want to use in the syntax of that LoD:
As to your other concern, no I would not export out the results of an LOD with the expectation that those numbers are going to line up. You are going to see the duplication in the rows of data on an LOD export like that. Please remember that Tableau knows that the row level data value displayed is a single value only, even though displaying multiple rows of the same value; confusing yes, but just know that the aggregated Total is correct. You would read this as...
In Illinois for Customer ID FH-14275 had overall sales of $4,256.27 for all time, but in Illinois had a single sale for $7.99. For Customer ID JK-16120 had overall sales of $2,932.48, but in Illinois had multiple sales of $124.79, $83.95, $480.96 = $689.70
Hope that explains a bit more as to how LOD's work and display data/values. Best, Don
