We've got a couple options available to us to get the percentage values. I've attached a revised copy of your workbook with two solutions. The first is to create a separate worksheet for the percentage value and then combine both worksheets on a dashboard. For the table calculation we will need to set the Compute Using to [Measure].
The second option is to duplicate the [Measure] dimension and use a trick with the SIZE() function to force the percentage calculation into the Grand Total row. This requires you to set the Compute Using setting for the calculations to use [Measure (copy)]. Both solutions require us to use the LOOKUP() function to calculate the values.
Another option would be to pivot the columns when you connect to the data. This might make things easier down the road but it's not a requirement if you would prefer to keep the data in its current structure.
Hopefully this help get you started. Thanks Collin!
PercentageQuestion_REVISED.twbx 50.2 KB
Thank you very much for this. I have managed to implement the first suggestion that uses the dashboard and calculation and I think I understand. I have a few questions on the second approach and will follow up with these tomorrow.
Sorry for the delayed response! As mentioned I implemented your approach one above which is working for me but I am now trying to understand it. First let me try to understand your option one before moving on to the more complex option two.
I may be confused by the way the partition is working which I believe is dictating how this calculation processes the data. Is it a separate partition per measure? E.g. The formula (SUM([Q216 PRELIM]) /LOOKUP(SUM([Q216 PRELIM]), -1)) * 100
is calculating based on the two separate partitions shown in red below?
In which case I believe that the first part of the formula which is the SUM([Q216 PRELIM)) is bringing back 43.1 and 14.3 separately. Is this correct?
If so the second part of the calc. using LOOKUP appears to be looking at the previous row and dividing the two. So new calc record = record 2 / record 1. Is this correct?
If I have the above correct I am confused as to what is happening for the first record. Surely this would mean the first part of the calculation finds 43.1 and then the second part looks for a prior row or row 0 which does not exist?
What happens here, does it just ignore the calc. as no row 0 exists?
If I have the above totally wrong is there any chance you could provide some guidance on how the calc. is processing the different rows?
Once I have the above clear in my mind I will move on to the second option.
1 of 1 people found this helpful
You've pretty much got it in terms of what's going on. I'll try to explain in another way though just to make sure.
So the way LOOKUP() functions in relation to the (SUM([Q216 PRELIM]) /LOOKUP(SUM([Q216 PRELIM]), -1)) * 100 formula is that it it will evaluate for each row, once for the Sales value (43.1m) and again for Std Margin (14.3m). When the calc is evaluating for Sales it will effectively be doing this:
(43.1m / (value that does not exist) ) * 100
Since we are trying to divide by a value that isn't there (the cell previous to Sales does not exist) Tableau will return a result of null.
When we move on to evaluating the result for Std Margin, the formula will be doing the following:
(14.3m / 43.1m) * 100
So this calc actually does return two different values per column, it's just that one of the values is null so it doesn't appear on the worksheet. If you take a look at the "2 sheets + dashboard" worksheet and look in the bottom left corner, we can see that there are indeed 4 marks being rendered but two of them are hiding out in the realm of null
Hopefully that makes things a bit clearer on how LOOKUP() is doing the math.