1 of 1 people found this helpful
It's difficult to give you an exact solution without seeing the 'shape' of your data...However I think this will help.
So the calculation you have is an AGGREGATE calculation. As you have the SUM() wrapping your values. As such the result is dependent on the VizLoD. For example, thinking Superstore data set, if you had an AGGREGATED measure, SUM([Sales]), and brought this into your Viz, it would show the total sales for everything, if you then bring in Region you then get 3 values for each region. So in this way the result isn't calculated until you use it...However row level calculations are run 'Off Canvas' (as I've heard it called and really like the term). So we can put the Row Level part within your formula. One for Sector A and one for Sector B, and then these use these fields in your Row and Column Shelf and then control the 'Grain' (number of points) by dragging the appropriate dimension onto the detail shelf.
So you'd have
[Sector A Change]
(SUM(IIF([Sector] = 'A', [FY2015], NULL) )/ ((SUM(IIF([Sector] = 'A',[FY2014],NULL))+SUM(IIF([Sector] = 'A', [FY2013],NULL))+SUM(IIF([Sector] = 'A', [FY2012],NULL)))/3))-1
and for sector B
[Sector B Change]
(SUM(IIF([Sector] = 'B', [FY2015], NULL) )/ ((SUM(IIF([Sector] = 'B',[FY2014],NULL))+SUM(IIF([Sector] = 'B', [FY2013],NULL))+SUM(IIF([Sector] = 'B', [FY2012],NULL)))/3))-1
you may need to double check the brackets as I've not used the Calculation Editor (which has made me pretty lazy as it tells me where I've missed one!!)
Hope this does the trick, but please post back if not (ideally with a mock up of your situation)
2 of 2 people found this helpful
First off, welcome to the community! You're gonna love it here, I promise!
This is totally possible in Tableau, but how we do this can depend on how your data is structured, and what the overall end goal is.
If possible, can you provide an example workbook? This will give us the ability to demonstrate a solution that best fits your data structure and criteria.
If you have sensitive data, than you can create an anonymized version of it using this guide
It worked perfectly!
How come did IIF was able to do the trick?
I have attached a sample workbook to illustrate what I intended to do and it worked well both in the attached example and my actual data.
Tableau Example - SMA.twbx 14.3 KB
Excellent news!...so by way of an explanation I've attached a simple mock up in Excel
So the IIF part is being computed at a Row Level, as per the 2 IF statements in the Excel. But then we are asking it to AGGREGATE (by way of a SUM) these results and return us that, which is the SUM of the 2 columns (equivalent to your 2 measures) shown below the data. Then like the Region/Superstore example I gave earlier the actual value these take is dependent on the Level of Detail of the Viz (known as VizLoD), you are choosing the VizLoD via the detail shelf (and also the colour, shape, size)
Hope that makes sense
Embedd Row Level IF.xlsx 8.6 KB
I see, this makes a lot more sense now. Thank you for the help!
Cheers mate...should have given credit to the inventor!!
but LOVE the term...it's multiplied, many times, my ability to explain the differences between calculation types in the training I give...In fact it's the starting point of the calculations section...just 2 simple words, and people go from to !!
That's awesome! Well I'm glad that it has helped you out!