14 Replies Latest reply on Dec 21, 2015 4:22 AM by galaxy.tab

    Add a measure - LineItem

    galaxy.tab

      Hi All,

       

      Attached is a sample workbook , where I want to show "Profit Ratio:" field as a new lineitem.

       

      So I want it to be shown above furniture as first line item with value on the right side. Please advise how a tabular report can be achieved.

       

      Sample :

       

      Profit Ratio        2% 

      Furniture          $742000

      Office Supplies     $719047

      Technology     $836154\

       

      Thanks

        • 1. Re: Add a measure - LineItem
          kumar.c

          Hi,

          Profit ratio is a measure so when you add it on to the sheet it will show profit ratio for every member of the dimension, like below:

          Profit Ratio.png

          • 2. Re: Add a measure - LineItem
            galaxy.tab

            Hi,

             

            Yes. By profit ratio I just meant an example. I need a measure to be displayed above the furniture.

             

            Any workaround on this ?

            • 3. Re: Add a measure - LineItem
              Norbert Maijoor

              Galaxy!

               

              Find my approach in attached workbook version 9.2

               

              line2.png

              1 of 1 people found this helpful
              • 4. Re: Add a measure - LineItem
                kumar.c

                Yes, you need to create a calculated field for different member of the dimension like Furniture, Office Supplies and Technology so the calculation will be If attr(Category)='Office Supplies' then Sum (Sales) end like this create three and drag the measure names onto rows and measure values onto text and filter whatever measures you want to show.

                • 5. Re: Add a measure - LineItem
                  galaxy.tab

                  Thanks Norbert.

                   

                  What if I have multiple measures to be shown on different columns ?

                   

                  I need to display new 4 measures

                  • 6. Re: Add a measure - LineItem
                    kumar.c

                    Hi,

                    Keep the measure names onto columns

                    • 7. Re: Add a measure - LineItem
                      galaxy.tab

                      Simon Runc Can you please help !

                      • 8. Re: Add a measure - LineItem
                        Rody Zakovich

                        Hello,

                         

                        What exactly are you trying to accomplish with this?

                         

                        Both from a Technical perspective, and data visualization perspective?

                         

                        Regards,

                        Rody

                        • 9. Re: Add a measure - LineItem
                          galaxy.tab

                          Hi Rody,

                           

                          So the dataset I have has a measure , the value of this measure is to be displayed in the first row.

                           

                          i have another dimension field "ABC" so this needs to be displayed below the measure and the values to be shown to the right side as I have mentioned above.

                           

                          Thanks

                          • 10. Re: Add a measure - LineItem
                            Rody Zakovich

                            So you want to treat a measure as though it were another Dim?

                             

                            This maybe possible by setting up each Dim as a Measure (Then utilizing Measure/Names Values as described above), then using the MIN(1) technique for each Measure you want to display on the columns, but it will restrict freedom with the data. And you will need to create the Profit Ratio with  a FIXED LoDs, so that you can reference it as though it were a Dim.

                             

                            But this "layout" of the data seems confusing. What is the end goal from a Visualization perspective? Is there any possible alternatives you can use to visualize this in a not so "Excel" way?

                             

                            Regards,

                            Rody

                            • 11. Re: Add a measure - LineItem
                              Simon Runc

                              hi Galaxy,

                               

                              Can you post an example of a multi-column version (just a table in Excel, or as above just 'free-text') so we know the final version you are trying to get to. 2-3 columns is fine, it's just so we can see if these need to made up from seperate calculations (as Norbert has done) or if we might be able to use the 'dimensions' and have less calculations.

                               

                              Thanks

                              • 12. Re: Add a measure - LineItem
                                galaxy.tab

                                Simon - As requested please find a sample workbook.

                                 

                                I want to combine the Category Dimension with Sales measure.

                                 

                                So in the 3rd sheet in the workbook "Category+Sales" , I will need to show sales as a row.

                                 

                                Please see the below screenshot

                                 

                                 

                                So just trying to see if sales can be shown as the first line in the tabular report and the value in first column, further I will also be creating a calculated field that will do Sales+Furniture+Office Supplies + Technology and display as a new row at the bottom.

                                • 13. Re: Add a measure - LineItem
                                  Simon Runc

                                  So yes it can be done, but soon gets very complicated and messy (Tableau isn't really the tool for this kind of thing, but as always with Tableau due to the clever way it thinks, you can 'hack' together most things!). If I had to do something like this, I'd probably do it with different sheets and make up the view I wanted by combining them in a dashboard.

                                   

                                  However, attached is a solution (of sorts!) in a single sheet.

                                   

                                  So here I've created an index calculation, and ran this by Category (which is in my VizLoD) so each Category gets the index 1,2,3. I bring this in as a discrete field, and also bring in MIN([number of records)] as a 'continuous' field, which I'll use to plot out my view (this is the MIN([1]) trick Rody referred to.

                                   

                                  I then create a series of formulas utilizing this index. This determines if I want the figures on every Row, or just the top. So for example my total sales at the top is

                                  [Sales Total]

                                  IIF([index]=1,TOTAL(SUM([Sales])),NULL)

                                   

                                  I then address/partition the index by Category (so it counts 1,2,3) and the TOTAL(SUM([Sales])) by Table. As NULLs don't get plotted this has the affect of only adding this to the Index = 1 (the top Category). I then do similar formulas for the Ref Line (which is what adds the divider on the Top row), and the Sales Label (which again we only want for the top row). I bring all these fields into my text tile, and arrange them so I get the desired view.

                                   

                                  As I say, not pretty, but hopefully this gives you the various tools (as you can see all the fields are a variation on the same structure). if you want to go down this route.

                                  • 14. Re: Add a measure - LineItem
                                    galaxy.tab

                                    Excellent !