11 Replies Latest reply on Jan 14, 2020 1:27 PM by Allison Bockman

# Use 2 dimensions to sum columns separately to calculate delta

I want to make a chart with 3 levels: CAGR (circle), 1st year production (width of bar) and production delta from 2020 – 2025 (height of bar).  The viz looks the way that I want it too, but the numbers are not correct.

My issue (in the attached workbook, the viz is different than the one I created and shown above) is that I can’t determine how to sum the volume by the sales parent by an individual year, for example Company A with 10,000,000 production for only 2020.  And since I can’t sum by year, I am left with the width of each bar being the total across the years in the dataset for each sales parent, and the years range from 2001 – 2031.

This results in no bar height because I can’t determine the delta and 0% CAGR because I can’t separate the years from each other.

For the first year, 2020, I have the following calculation (named First Value Sales Parent in workbook):

{FIXED [VP: Sales Parent], [Years]=#2020# :

SUM([Volumes])}

For the last year, 2025, I have the following calculation (named Last Value Sales Parent in workbook):

{FIXED [VP: Sales Parent], [Years]=#2025# :

SUM([Volumes])}

My calculation for the delta is:

[Calc - Last Value Sales Parent] - [Calc - First Value Sales Parent ]

My calculation for CAGR is:

{FIXED [VP: Sales Parent] :

AVG(

POWER(

[Calc - Last Value Sales Parent] / [Calc - First Value Sales Parent ],

1 / DATEDIFF('year', [Calc - 2020], [Calc - 2025])

) - 1

)

}

I have tried multiple permutations of these formulas but no luck – still lots to learn about calculations/LOD/AGG , etc. – thanks for your help!

Message was edited by: Ben Kronk

• ###### 2. Re: Use 2 dimensions to sum columns separately to calculate delta

Thanks for catching that - it's now attached.

• ###### 3. Re: Use 2 dimensions to sum columns separately to calculate delta

Ben Kronk Can you paste your screen shot again with the columns, rows, and marks card visible? Also wondering if you'll always want to look 5 years into the future (i.e. if you're looking at 2020 then you want to compare the 2020 value to the 2025 value for your CGR, and in 2025 you'll want to compare 2025's volume to 2030's)? Will you exclusively look at one year at a time, or will there be instances where you'd like to see multiple years?

• ###### 4. Re: Use 2 dimensions to sum columns separately to calculate delta

Okay no problem - I added an updated viz.  To answer your question, I just want the one 5 year period for CAGR, and I just want to look at 1 value for the first year's production, which I want to represent by the width of the bar.  Thanks!als

Allison Bockman

• ###### 5. Re: Use 2 dimensions to sum columns separately to calculate delta

Ben Kronk I've made progress, but hit a snag getting the CGR calc to work. Asking a friend who worked in Finance if he encountered this too. Will have an update for you ASAP. Sorry for the delay!

• ###### 6. Re: Use 2 dimensions to sum columns separately to calculate delta

Ben Kronk on sheet 4 of the attached workbook should be the view you're looking for. It'll likely look better when you've filtered to a smaller number of Sales Parents. I used parameters so your years can change dynamically (rather than hard coding each year which requires  additional work next year if you want to use the same logic). Also, if you right click [Years] before you drag it out it'll give you the choice of field types including COUNTD, which means you wouldn't need a separate calculation for that.

• ###### 7. Re: Use 2 dimensions to sum columns separately to calculate delta

Thanks for working on this!

I checked the updated viz and the look is correct, but it's still summing all of the years, even with the addition of the parameters.

For example, Ford sums to ~175 million (the bar height, with every year added up) but should instead sum to about 5.2 million (just the 2019 value).

I added a new sheet where I filtered by year and sales parent so it's more manageable.  I also added a crosstab sheet (based on the filtered sheet), which shows that the delta and the 1st year volume are the same number, and they should be different numbers, so that's something else.

Also, this is strange, but when I labeled the bars or the CAGR circles, the label is 0, even though this is not what the visual elements show.

I uploaded a new .twbx, labeled now label Tableau Question v1.

I'm still working on this, thank you again for all of your help!

• ###### 8. Re: Use 2 dimensions to sum columns separately to calculate delta

Ben Kronk I'll try to take a look at your new workbook tonight. Are you still having issues?

• ###### 9. Re: Use 2 dimensions to sum columns separately to calculate delta

Ben Kronk the reason your bar height and labels were off is that the field used was [Volumes] not [Starting Year Volumes]. The latter will change dynamically based on the year you've selected from the parameter control (it looks exactly like a filter) on the top right. It looked like having two parameters might be confusing so I changed it to one, and edited the [Ending Year Volumes] calculation to reflect the [Starting Year] + 5.

As for the CGR label of 0, that was because there were two CGR calcs (your original, and the one I added). The original was still on label. Not sure how often you use dual axis, but I used to forget all of the time that there are 3 different marks cards. One for all of the viz, one for the first measure, and one for the second measure. This allows you to make modifications independently (for instance, when you want to see circles on a map).

On the crosstab, I changed [Volumes] to [Starting Year Volumes] and updated the calc for the delta to include SUM around both [Starting Year Volumes] and [Ending Year Volumes] and I think it works now. Please see attached.

1 of 1 people found this helpful
• ###### 10. Re: Use 2 dimensions to sum columns separately to calculate delta

Yes!  Thanks Allison Bockman that's exactly what I've been trying to do - my manager loves it!

Thanks so much for all of your help, and especially the detailed commentary, I learned a lot!