12 Replies Latest reply on Jan 23, 2019 8:19 PM by Mahfooj Khan

    How to divide by total sales amount

    Alex Martino

      Hello. I have two data sources.

       

      #1 Sales data. This has sales information.

       

      #2 Cost data. This has cost data by category.

       

      I blended the two data sets on month. HOWEVER, I need to be able to create these metrics and I don't know how. 1) Total category cost per sales for ALL the data. The problem is that if a specific category didn't have expenses in a given month, then the sales # is less than what the total actually is. Is there any way that I can fix

       

      I need to do this same thing at the monthly level as well.

       

      Sheet attached.

        • 1. Re: How to divide by total sales amount
          Data Analyst

          Hi Alex,

           

          Do you have sales data by category?

          • 2. Re: How to divide by total sales amount
            Alex Martino

            Hello. No, the category is for expenses. I'm trying to get to cost per category per sale. So if there are 1,000 sales and $50 of expense in a category. That would be 5 cents per sale.

            • 3. Re: How to divide by total sales amount
              Mahfooj Khan

              Hi,

               

              Is that what you wanted?

              Find my approach in attached workbook. I've joined your sources instead of blend. Then created a calculated field using LOD{} expression to get the Sales per Category (Sales New). Finally Cost per Category (Amount/Sales New).

              Let us know if this work.

               

              Mahfooj

              1 of 1 people found this helpful
              • 4. Re: How to divide by total sales amount
                Alex Martino

                The join might be the solution. However, I want the sales isolated by month and then by total. So one version should show any "amount" breakdown broken down by total sales. The other version should show any breakdown divided by monthly sales.

                • 5. Re: How to divide by total sales amount
                  Mahfooj Khan

                  I don't know how much I've understood your requirement. If you can explain with an example then it will help me to create the expected view for you.

                  • 6. Re: How to divide by total sales amount
                    Alex Martino

                    I added the spreadsheet with my expected result. It worked.

                     

                    Question though: What is the difference between the join and the blend in terms of being able to create the fixed formulas?

                    • 7. Re: How to divide by total sales amount
                      Mahfooj Khan

                      Hi,

                       

                       

                      Difference between joining and blending data:

                      Joining your data can only be done when the data comes from the same source, for example from two sheet tabs within a single Excel file or two system tables from oracle database or MS SQL Server db. If that same information was stored in separate Excel files or in two different database servers, you would need to do a data blend in Tableau. A blend is always required if the data is stored in two separate "data sources" within Tableau. So even if your data is very closely related and exists in two separate files or databases, you will have to do a data blend if you are combining the data in Tableau.

                       

                      When blending data, the first data source used in your view will dictate how your worksheet view in Tableau is built. The secondary (blended) data source will be able to contribute extra information, but will not be able to change the overall structure of the view. The secondary data source's values can be aggregated and applied to the existing view after you have established a "relationship" by assigning a variable that both the primary and secondary data sources have in common.

                      While using data blending there are some data blending limitations around non-additive aggregates, such as COUNTD, MEDIAN, RAWSQLAGG, LOD{} expression, table calculation etc. Follow the link for more details Troubleshoot Data Blending - Tableau

                       

                      You need to understand when you should use data blending or joining

                      Try to avoid data blending when you can join the two data sources outside of Tableau. If not, then you must have at least one common field shared by the two data sources you want to blend together. Whenever possible, go for a join instead of blend. If you need to combine two data sources and for whatever reason cannot manage to join the data outside of Tableau, your only option is a data blend.

                      As and example: (1) a data source with three columns including Category, Sub-Category and Sales, and (2) a data source with Sub-Category and detailed information about each Products sales. You could get Category sales using (1) and then blend in extra supplemental information using (2), where a relationship is built by connecting the data sources based on the Sub-Category.

                       

                      You can only use joining when your data comes form the same underlying source (for example, the same Excel file or Access file or any database).

                       

                      Hope this will help. Let us know if you've any further question else you may close this thread.

                       

                      Mahfooj

                      • 8. Re: How to divide by total sales amount
                        Alex Martino

                        Hi @Mahfooj are you sure about the excel files? I can add one file and then I added another file into the same data source and it seems like I can join them.

                         

                        Additionally, if I'm not joining the data, how can I get around the issue I described above (since you joined the data)

                        • 9. Re: How to divide by total sales amount
                          Alex Martino

                          This is a picture. I added two sources and they are both showing up to be joined. Screen Shot 2019-01-23 at 7.23.52 PM.png

                          • 10. Re: How to divide by total sales amount
                            Alex Martino

                            And actually, the files that were in the join that I attached, were also from different excel sheets and it worked fine......

                            • 11. Re: How to divide by total sales amount
                              Mahfooj Khan

                              Yes! You can join one excel file with another one.

                              • 12. Re: How to divide by total sales amount
                                Mahfooj Khan

                                It should be like this, you need to drag the worksheet (Data/Tables) from New Financials source then drag the Transaction Details. Tableau will established the join then you need to validate the join condition. Set up the join condition then you're good to go with further analysis. Let us know if this help.

                                 

                                 

                                Mahfooj