Thanks for the sample workbook.
Instead of filtering on the filter shelf, move the filtering inside the calculation, like this:
Then you can use a parameter to select individual states without affecting the national totals. See attached.
car sales test - SW.twbx 41.6 KB
I would like to share another approach
Duplicate the same data source
Then using data blending, take the national average from the duplicated data source.
Please see attached worksheet.
car sales test.twbx 55.8 KB
Thanks for this. I had thought that "something" with a parameter would possibly work (removing the dimension filter). But I didn't get all the way there. I would like to get the dimension filtered data working though, as my actual data doesn't have a static set of values (state names) to filter on. Also, Sheet1 still doesn't work with a parameter.
Why your book works seems to make sense to me. Without the dimension filter, there are rows for each of those cells to aggregate. With the dimension filter though, there are no rows. However this seems to violate the documentation which says that FIXED should operate above dimension filters. If FIXED is broken and not obeying the documentation, that also makes sense with why Sheet1 is not working.. to my mind anyway..
Thanks, I'm really keen to read his reply.. I'd like to get a better understanding of how the the Fixed LOD calc works. I'm having a hard time getting my head around how the cell highlighted in the picture works. I'd figured the logic behind it would ignore the value of the State dimension and therefore calculate across all rows at Vehicle.Bus, Year(Date).2017, Month(Date).January.
2 of 2 people found this helpful
LOD expressions are subqueries joined to the query for the main viz e.g. your viz above would have a query like this (abbreviating date fields instead of writing the full DATEPART('year', ..) etc.)
SELECT state, yeardate, yearmonth, VehicleType, sum(national margin) group by state, yeardate, yearmonth, VehicleType where state = 'Wyoming'
LOD calculation ignores the where clause so it is
Select sum(sales)/sum(profit) group by yeardate, yearmonth, vehicleType
Since there is no where clause (fixed ignoring the filter) this will compute the ratio for all states.
Now Tableau has to join these two tables to get the final viz and since shared LOD is year, month, vehicle type those would be the join keys.
Inner join means only the records that occur in both tables will be kept and rest will disappear. So even though fixed will calculate the correct sum for all states, if a given state hasn’t sold anything in April 2017 the viz itself having been filtered won’t have a row for April 2017. So LOD calc can’t bring the value it computed back into the viz.
The main viz determines what marks will be in the viz. LOD expressions just compute values to be associated with those marks. If viz doesn’t have a corresponding mark for something LOD expression computes, it has no place to be shown hence gets dropped.
I hope this explains.
I appreciate and understand the reply. I also don't want to sound like the classic complaining user because I hate being on the receiving end of that too, but I guess I'll have to be.
This is a "problem" I encounter in Tableau a lot. Another example that comes to mind is YoY (etc) table calcs only really work as expected if you use discrete time and show missing values. It just hurts to lose the awesome continuous time axis and get the terrible discrete time axis. And if one of our less technical users doesn't know to do this, it actually produces erroneous charts.
While I understand where you are coming from, and I know it won't be changed, this sure does give me a lot of pain and makes the product harder and less predictable to use. Tableau is mostly a really great product and I do like it a lot.. this is about the only complaint I really have. I think most of us just naturally expect each series/calculation to be treated as its own mark set. It is very tricky to keep in mind that Fixed is above dimension filter, but its marks ARE filtered by the dimension. While I can keep this stuff in my head and continuously think about how my calcs are affected by sparse data, my colleagues who are not developers can't, and their results from the product are less awesome than they could be... or risk being straight up wrong. The example in this case is, from a user perspective, (less seriously) wrong: not showing national average where one exists. I understand the technical reason, but for me as the guy sitting in the seat with users complaining to him about their dashboard, it is wrong. It gets worse where I try to show an "above/below average" calc instead. The table calcs shown on continuous time axis produce VERY wrong results: marks in the wrong place.
Thanks for your time. I think the user above that suggested blending the data to itself is about my only workable solution.