![]() Unfortunately, pivot tables don’t get along with Tableau (eg. ![]() I bring the data in, Pivot it on the 3 measure names. Next we bring in the measure lookup (the mapping of measure names) and join that into the pivotted data. Reach out to me on Twitter if you can think of other use cases, and feel free to download this sample workbook if you’d like to take a closer look.In particular, I consent to the transfer of my personal information to other countries, including the United States, for the purpose of hosting and processing the information as set forth in the Privacy Statement. It’s a pretty simple technique that likely has other applications. … this “logical pivot” preserves the original granularity (one row per study), so my budgets remain unchanged: The sort is dynamic, so it adapts to filters (rather than the fixed alphabetical sort that I was limited to with the original data source).Īs an added bonus, while a physical pivot would have repeated each study budget three times (see below), forcing me to be careful with my aggregation or introduce level of detail calculations … With this calculated field, I can sort the Race dimension by Race Values, effectively mimicking a sort of Measure Names by Measure Values: ![]() The relationship forces Tableau to include each study in each Race row (Asian, Black and White) and draws the right measure from my original data source to show accurate totals: I then built a bar chart with the race dimension from my helper file on Rows and my Race Values calculated field on Columns. And I connected it to the Asian, Black and White columns from my original data source (which are measures showing the number of people enrolled): I based it on the Race column from my helper file (which is a dimension with Asian, Black and White as members). With this relationship, each study in my original data source relates to three different rows in my helper file, but the logical relationship doesn’t physically create the two duplicate records in gray:Īfter setting up the relationship, I just added a simple CASE statement. In Tableau, I then related the original data source to the helper file on Study Number: In my case, that meant one row per study and racial group, turning my racial groups into a single Race column, like this: Your helper file should have the same basic structure that you’d want from a pivoted data source. All you need is a simple helper file and a CASE statement. While you can’t technically sort Measure Values by Measure Names, there is a way to mimic it using the new relational data model that Tableau introduced in version 2020.2. I wanted a dynamic sort by Measure Values that would adapt to filters, but I was limited to an alphabetical sort since you can’t sort Measure Names by Measure Values … or so I thought, until my colleague Carl Slifer showed me the way! I included a bar chart in my dashboard showing racial breakdown, but I ran into limitations with sorting. My data source had one row per study with a number of different dimensions and measures about each study, including the number of study participants from different racial groups. I was working on a project analyzing data about medical studies. With a simple helper file and a CASE statement, you can mimic the structure of a pivoted data source while retaining the original structure and granularity. Note: You’ll need to be using Tableau 2020.2 or later since this technique requires Tableau’s new relational data model. In the example I’ll walk through below, it helped me sort Measure Names by Measure Values without pivoting my data. This technique is mostly useful when you need to pivot part of your data, but you don’t want to change the granularity of your data source. This blog will take a look at how to use Tableau’s relational data model to pivot data without duplicating records. Pivoting without pivoting … it’s not a trap or a trick.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |