4 Replies Latest reply on Aug 26, 2018 6:08 PM by Eric Viglotti

    Calculated attribute on sum of measure at a level of granularity

    Eric Viglotti

      Hello,

       

      I want to look at some measures to calculate an attribute and use that to group the data to look at other measures. For example, if you look at the attached Excel file derived from the attached Tableau file, I can visually see that there are 20 customers who life to date have had over $10K in sales. But I want to make a cross tab or other visualization that says:

       

      Customer Type     Customer Count      Sales    Sales Forecast     Profit %

      Over $10K             20                         $264K          XXX               XXX

      Under $10K           773                       $2,023K          XXXX               XXX

      Total                         793                         $2,297K          XXXX          XXX

       

      What's the best way to do this?

       

      Thanks!

        • 1. Re: Calculated attribute on sum of measure at a level of granularity
          Jim Dehner

          hi

          see the attached

           

          it returns this

           

          start with this to fix the sales at the cust level

          then this

          then this for the % profit

           

          and make the viz

           

          Jim

           

          If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

          • 2. Re: Calculated attribute on sum of measure at a level of granularity
            Eric Viglotti

            Jim,

             

            This is outstanding, thank you! I knew this would be something "fixed" would help with but wasn't quite sure the implementation. A few followup questions if I may:

             

            1. Why does fixed have to be used twice? I see it is in "jd fix sales" and also again in "jd label over and under".

             

            2. What if I want to do the over/under slightly differently...This time I want to group by customers who had over $5K in sales for only Furniture. I still want the table to have the same data in terms of showing the total sales $2.2mil, etc, but this time instead of it being split into 20 vs 773 customers, it should be split into 6 vs 787 customers because I know that only 5 customers had over $5K of sales in just Furniture. Again, the key here is I don't want to just filter the worksheet as that would lower the sales, profit, etc. I just want to factor in Furniture only for the purpose of splitting customers into groups. I thought the secret was in the comma/condition stuff I saw here Fixed Level of Detail by dimension over specific time period , but I couldn't quite get it to work. I know that I can cheat by doing a calculated measure that is "if category = furniture then sales else 0" and do the fixed on that, but it seems from this link that you can do this with commas and conditions within the fixed formula directly which is a bit more streamlined.

             

            3. Now how about the inverse...Let's say I want to keep this exactly as you have it with exactly 20 customers with over $10K of Life to date sales. However, now I want to filter for just a specific product and see what the sales was for those broken down by your over/under $10K group and customer. When I do this, I actually DO get the correct result, but I wonder, how is this possible? Is FIXED really unaffected by filters? It sure looks like it as Bill Shonely's total sales on this specific product is less than $10K but yet it's still in the over $10K group which is what I want (because his LTD sales for ALL products really is over $10K). Again, this actually is exactly what I want, but I'm really surprised it would work like this. I'm much more familiar with other BI tools where once you add a filter on a report, absolutely 100% of the calculations and items are subject to that filter can't "break out" of the context of that filter to determine a bigger picture categorization.

             

            Thanks again!

             

            Screen Shot 2018-08-25 at 4.11.09 PM.png

            • 3. Re: Calculated attribute on sum of measure at a level of granularity
              Jim Dehner

              good morning

              the first jd fixed just "fixes" the total sales at the customer level - the second applies a label to each customer based on the first - could you combine them into a single calculation - sure - I just made it 2 for clarity

              2if you want to vary the threshold - yo can do it with a Parameter and have the user input the threshold value then the conditional statement will reference the parameter name not 10000

              3 the second "Fixed" calculation results in a label that is tied to each customer record in the data set - all other dimensions or measures tied to the customer name can be filtered by the label - think like an excel table with a new column for label but all the other columns stay the same - you would be able to do a sum if, coutnif type calculation based on the label - same idea but the "table' looks a lot different

               

              Jim

              If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

              • 4. Re: Calculated attribute on sum of measure at a level of granularity
                Eric Viglotti

                Thanks so much Jim.

                 

                On question #2, sorry for confusing you. The issue was less about making the threshold be a different value via a parameter. The focus was more about how to make the threshold be for over $5K of sales in just Furniture. I don't want to filter the report for just Furniture, as I want the total sales and all of the measures to still be on the entire dataset. Rather, I just want the threshold calculation for the over/under to have this Furniture criteria. It sure seems like this url that talks about criteria within the fixed function would do that, but I couldn't quite get it to work. Fixed Level of Detail by dimension over specific time period Am I on the right track?

                 

                Thanks!