Post a sample workbook. And the version of Tableau you are using. There are too many moving parts to make suggestions without doing it in the context of the sheet design and data layout you have.
I couldn't log into that.
Extract it and upload it here.
1 of 1 people found this helpful
OK, I see what's going on.
Because you have only one pill ( SUM(Values) ) on the text shelf, you're only going to get one type of aggregation: SUM. That's all it can be.
So I'm just thinking out loud here... You'll need a calc of some sort to collect different types of aggregations based on the [Measures] in question.
Can you give me two scenarios that you might want to do? One that will do sums, and one that will do averages. Specifically, what would the user select to cause the need for an average of Median Distances, and conversely, what would cause a sum of "Something else". When I understand better what could happen, I can figure out if we want table calcs or LODs to do the job.
Once I can hack up a calc that will capture the proper aggregations, we can do AGG([That Calc]) instead of SUM([Values]) on the text shelf.
Yes, you got that right.
So when a user selects "State" in the "Select a region" drop down and then clicks on the map of MN, you will see the measure table on the right in the dashboard will change and currently it just gives all the counties, not an aggregated state number. I would need a "Sum" calculation when the user selects a measure category from the drop down that might have fields like total population or total households. On the other hand, if they select Housing and Homelessness and want to know "Median Gross Rent" for the state, then they would want to look at an average value. So I feel like different measures will either need a sum, or average or even percent of total calculations.
I can make a lists of measure fields that need an aggregation or sum or percent of total, if that will help?
Do you think since I have grouped the measures it will make it difficult to do separate calculations?
The grouping might complicate things, but I think it's a clever way to do what you are trying to do and I don't want to abandon that.
Let me look at this.
Thanks a lot!! I really appreciate the help!
Take a look at what I did here.
(See Sheet 6)
First of all, I moved Median number of miles into the Number of veterans group just to have two things to compare at one time. It probably doesn't make sense for your end goal, but it lets me mess with two "competing" measures together.
First thing I did was change the hierarchy to start with State. And then I put STATE on filters and excluded null.
As a test I created two calcs: [Number of veterans aggregate...] One is a table calc, and one is LOD. I display them in the title for testing purposes.
Both work correctly. And since they do, we're better off going with LOD here so that we don't have to mess with the addressing directions of the table calc.
So next I created a calc called Calculated Values. This is the calc I was referring to earlier. Take a look at that. I do different LOD expressions (like I did in the [Number of veterans LOD]) for each measure. One is a SUM. One is AVG. You can do the same for all the different measures you want to mess with.
Notice the display on Sheet 6. The old [Values] sum changes as the hierarchy is expanded. But the calc value doesn't change. This isn't the end of the game here. I just wanted to show you how you can manipulate your data.
Keep expanding your hierarchy to [Tribal Nations]. You'll notice that the LOD calc no longer looks right. That's because it currently doesn't take into account [Tribal Nations]. You'll need to add that in the "include" list in the table calcs.
And if you want to see the numbers roll up to the CoC Region level instead of STATE, you would take that variable OFF the INCLUDE list. (So yes, you would need separate calcs to show the various levels. AND you would need separate sheets to show the various crosstabs, just like you are doing with the map sheets, and swap them accordingly like you are doing for the map sheets.) So really you would have a [Calculated value -- State] and a [Calculated Value -- CoC], etc. Right now what I made is rolling up to the state level. And it works until [Tribal] is exposed on the sheet because I have addressed all the dimensions that are always on the sheet (CoC and County by virtue of the filters, and State because it's always in the ROWS on this sheet.)
I hope this gets you aimed in the right direction. As you expand this out, ping back if you run into hurdles.
And if I'm not mistaken, you shouldn't need different sets of calcs based on which set the user selects. One giant calc for all the measures should do it all, no matter which set the user chooses.
2017_Jain A.twbx 189.6 KB
Thanks a lot Joe!!! I am implementing the solution you suggested.. I was not familiar with LOD so trying to make sure I get all the calculations right. I will get back to you in a day to share progress.
Thanks a ton!!
Your solution worked perfectly! I made separate sheets for CoC and State and aggregated it using the If .. then and LOD formula that you had put together. Then I linked it the same way I had done it for the maps..Thank you SO MUCH!!!! It works like magic