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?
Jun 24, 2016 12:17 PM
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:
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.
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
Jun 24, 2016 12:24 PM
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:
Top 10 Tableau Table Calculations | Tableau Software
Best,
Hil
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.
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.
Jun 24, 2016 12:50 PM
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:
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.
Jonathan
Thank so much! Not being able to do LODs across data sources seemed like a dead end. Your solution worked like a charm.