6 Replies Latest reply on Apr 24, 2017 1:58 PM by Ron Eisenstein

    how to aggregate blended measures?

    Zach Leber

      I have two data sources, one with costs, the other with sales. I blend the costs with the sales to determine for each sale whether it was above or below cost. But I am unable to create an aggregate calculation that determines the percent of sales that were above cost, because I can't aggregate a blended measure, as blended measures must already be aggregated. Here's a screenshot and a 9.3 TWBX. Any ideas?

      aggregating blended measures.png

        • 1. Re: how to aggregate blended measures?
          Jonathan Drummey

          Hi Zach,

           

          You're going to need table calculations for this. The reason why is that the blend is on aggregated data, so to aggregate over the aggregated data we need something else. LOD expressions can do this but only act on a single source, to work across sources we have to use a table calculation.

           

          Note that I turned Aggregate Measures back on in these views and added Order Number to the view as a dimension. Here's the % over Cost measure:

           

          WINDOW_SUM(IF SUM([sales price]) >= SUM([costs].[Cost]) THEN 1 ELSE 0 END)/SIZE()

           

          The numerator counts all the addresses (marks) that meet the criteria and then SIZE() counts the total number of marks. Here's the view:

           

          Screen Shot 2016-06-24 at 3.09.40 PM.png

           

          If you want to just show a single total then we still need the Product and Order Number as dimensions in the view so the calculations will work, we just need to filter out everything but one mark. I set up the % over Cost (single) calc with the formula

           

          IF FIRST()== 0 THEN

              WINDOW_SUM(IF SUM([sales price]) >= SUM([costs].[Cost]) THEN 1 ELSE 0 END)/SIZE()

          END

           

          so it only returns a single non-Null mark, then brought it into the view and set the Compute Using to be Product, order number.

           

          Screen Shot 2016-06-24 at 3.10.59 PM.png

           

          The filter for the table calc is set to filter for non-Null values so it only shows a single value, I also turned off the tooltips for Product and Order Number.

           

          I notice you're using extracts for this, an additional option coming in v10 would be to use the cross database joins and generate a combined extract.

           

          Jonathan

          4 of 4 people found this helpful
          • 2. Re: how to aggregate blended measures?
            Hildebrando Souza Jr

            Hi Zach,

             

            It looks like you deactivated the Aggregate measures option on Analysis menu. If you activate that and bui a table as shown on the attached workbook, you will be able to use a quick table calculation on "Number of records" to give you the percentage you want. It's also possible to isolate the good sales result on an calculated field, so I just corrected you calculation. Check out the following links for further information:

             

            Aggregating Data

            Quick Table Calculations

            Top 10 Tableau Table Calculations | Tableau Software

             

            Best,

            Hil

            • 3. Re: how to aggregate blended measures?
              Zach Leber

              Thank you very much Jonathan, that works, though the complexity will probably drive us to get the blended data into our primary data source ahead of time. Our actual data sources are not 2 spreadsheets, but one published data source and one live database connection, so v10 cross-database joins will not work, as they don't work with published data sources.

              • 4. Re: how to aggregate blended measures?
                Zach Leber

                Hi Hil, thanks for the reply. I deliberately turned off aggregation to split the orders. I don't think your suggestion calculates the percentage of good sales however, it just calculates the percentage of sales. See Jonathan's reply for what I really need.

                • 5. Re: how to aggregate blended measures?
                  Jonathan Drummey

                  Hi Hildebrando,

                   

                  Your proposed solution only works given the particular set of data. The reason why Zach had disaggregated measures was to show the order number level. When we turn on aggregate measures again we have to be aware of the level of detail, in the bar chart view that is only at the Product level of detail can cause the results to be inaccurate. I prepared a demonstration of this where I revised the sales price for the order number 2 to be 80 and created a new Good Sale (broken) measure to use the revised sales price:

                  Screen Shot 2016-06-24 at 3.40.28 PM.png

                   

                  Note that in the detail rows for Product ABC there are now two different results, one is a Good Sale and one fails to meet the target. However in the Subtotal due to that calculation being at the Product level the Cost vs. revised sales price shows that there are two records meeting the target and that's wrong, only 1 actually is. The Grand total that computes across the entire data set is even more inaccurate.

                   

                  So the bar chart still shows 2/3 of the products being good sales when it should only be 1/3.

                   

                  Screen Shot 2016-06-24 at 3.43.52 PM.png

                   

                  Jonathan

                  1 of 1 people found this helpful
                  • 6. Re: how to aggregate blended measures?
                    Ron Eisenstein

                    Thank so much!  Not being able to do LODs across data sources seemed like a dead end.  Your solution worked like a charm.