Maybe not exactly as shown, but it's close. 10.2 version attached (as I don't know what version you're on). If this answers the question, please mark thread as correct to close it. Best, Don
Level of Detail (LOD) calculations are used to get number of legs and costs as roll-up for each Product ID:
Origin-Destination_v10.2.twbx 29.8 KB
You could look to do something like this using window functions and FIRST():
Workbook attached. But as Don said, this isn't Tableau's strong suit.
You could look to add the rollup count next to the Product ID instead, then it should work pretty easily. Eg WINDOW_SUM(SUM([Number of Records]))
RollupTable_v2018.1.twbx 39.3 KB
Does it matter if the product ID# has both numbers and letters?
My Number of Carriers is working but Nulls the empty space. Is there away to remove it?
Also the Total Cost is not SUMing the total cost per Product ID but keeping the distinct level of cost amount each row.
It should not matter as tableau only cares about you specifying the field.
I'm not sure I follow - when you say it Nulls it do you mean how it writes out "Null" instead of providing an empty string? This is why the calculation in the workbook I attached converts it to a string, unfortunately. So you can then return an empty string ("") so it appears blank.
What's your calculation for total cost? Here was my approach:
That is correct. "Null" appears in the next row under 2 instead of the cell being blank.
So my calculation for total cost is actually a calculated field taking the cost of another cost column and replacing the 0's and null with a pre-planned cost column.
The Total Cost is actually called "GGs Cost" and the calculation is
IIF(ISNULL([CostUSD OR ([Cost=0, [Planned Cost USD], [CostUSD] )
I put [GGs Cost] in the If statement you provided.
IF FIRST()=0 THEN STR(INT(WINDOW_SUM(SUM([GG's Cost]))))
The roll up of distinct legs do not work.