The formula for _NonVC $ is already an aggregate due to the SUMs in the formula, so changing this to a SUM on the pill is not possible. What is it you're trying to accomplish?
IF ([_NonVCRate]/100)* SUM([Chargeable Weight])< SUM([Rates (Rates)].[Minimum])
THEN SUM([Rates (Rates)].[Minimum])
ELSEIF ([_NonVCRate]/100)* SUM([Chargeable Weight])> (SUM([Rates (Rates)].[Maximum]))
THEN ([_NonVCRate]/100) * SUM([Chargeable Weight])
Trying to do similar report in the tableau.
I did calculate all the field example w/o VC = _NonVC but at Airbill
but need to summarize at the origin level
Please find attached tableau and excel file
Origin # of Airbills Tonnage w/o VC w/o VC FSC w/o VC (incl FSC) VC VC FSC VC (incl FSC) VC - w/o VC VC - w/o VC (incl FSC) % Rev Lost w/VC % Rev Lost w/VC (incl FSC) # Airbills Needed to Make Up Rev Tonnage Needed to Make Up Rev BNA 711 330694.5 $67,266.06 $11,435.23 $78,701.30 $66,820.84 $11,359.54 $78,180.38 ($445.22) ($520.91) -0.66% -0.66% 5 2203 CHS 138 57690.9 $11,933.67 $2,028.72 $13,962.40 $11,842.48 $2,013.22 $13,855.70 ($91.20) ($106.70) -0.76% -0.76% 1 444 GSO 789 240360.9 $53,366.56 $9,072.31 $62,438.87 $52,236.81 $8,880.26 $61,117.07 ($1,129.74) ($1,321.80) -2.12% -2.12% 17 5198 Grand Total 1638 628746.3 $132,566.29 $22,536.27 $155,102.56 $130,900.13 $22,253.02 $153,153.15 ($1,666.16) ($1,949.41) -1.26% -1.26% 21 8003
I still don't quite understand. I think my problem is that I don't fully understand your data or what the calculation is trying to accomplish. What numbers do you expect to see in the _NonVC $ column? Can you walk me through a calculation of this value?
Sorry for the confusion. I am trying accomplished is if I have sent a client data summary instead of all the data point in BNA(origin).
Saying Total NonVC = $ 67,266 for BNA origin similar for GSO and CHS origin.
So when I click on the _nonVC$ column and it gives me Sum of measure value $67,266 (see screen shot)
I want to create a table where all the data for _nonVC and _VC are summarized. similar Airbill number and weight (see below screenshot)
Let me know if you have any additional questions.
1 of 1 people found this helpful
You are just running into a math problem. Since your calculated fields are all based on aggregated data, then Tableau is aggregating all of it at once when you don't have the air bill number in the view. It's looking at the SUM of the weight, finding that it's huge and thus giving it the [Rates (Rates)].[7500Lb] rate. You want this to evaluate the weight of each air bill first, then aggregate it. To do that, you'd want to remove the aggregation from your calculated fields. However, since you're using blending to get the rates, that's not possible--blending requires aggregation. I think this may require some more significant changes for it to work, unfortunately.
What could be alternative? I don`t mind changing the data calculation significantly.
Can do Union between Tableau server data and excel or any other file type (MYSQL or CSV)?