
1. Re: Really UrgentCalculating Measure value with respect to a one particular value of one measure
Jonathan Drummey Mar 18, 2013 12:00 PM (in response to Monika G)Hi Monika,
I'm not sure what categories you are assigning your posts with, I suggest things like this go into Calculations so they aren't showing up in the Community Canvas that not so many people are paying attention to.
In any case, there are at least a couple of ways of going about this. The tricky bit is that you want to use the value from one row (the Total) for other rows in the view, and generally in Tableau that calls for table calcs. the "method 1" worksheet in the attached uses a table calcs with the formula:
PREVIOUS_VALUE(WINDOW_MAX(SUM(IF [Dimension1] == "Total" THEN [Act] END)))
The innermost part just does a rowlevel filter to return the Act for the Total row and Null for all other rows. Then the WINDOW_MAX returns that Act value across the whole data set. The PREVIOUS_VALUE is an optimization trick, so this calc is performed once for the first row and then returns that value to every other row.
An alternative method is to use custom SQL with a subquery to return the value Act for Total to every row, I set that up in a separate data source for the method 2 worksheet.
Generally, I'd use method 2 because it makes further computation on Act much easier.
Jonathan

total in calc.twbx.zip 43.7 KB


2. Re: Really UrgentCalculating Measure value with respect to a one particular value of one measure
Monika G Mar 18, 2013 11:22 PM (in response to Jonathan Drummey)Thank you Jonathan for your infirmation.