
1. Re: Percentage of other value calculation not of total.
Derrick Austin Mar 9, 2016 2:06 PM (in response to Josh Pyszkowski)Hey Josh,
I've attached an example of how to do this with LoD calculations.
Hope this helps!
 Derrick

TableauCalculationExample.twbx 17.2 KB


2. Re: Percentage of other value calculation not of total.
Josh Pyszkowski Mar 10, 2016 7:34 AM (in response to Derrick Austin)Thanks Derrick. That's and interesting calculation you came up with and works great...for one Opportunity...When I applied it to my actual data set with thousands of diff Opportunities the numbers went bonkers. My apologies, I should have given a more realistic data set. Attached is the workbook with more data and your calculation applied. Also you will see a rough mock up of the dashboard I am trying to achieve. Ultimately I need the pie charts to display the average % Differences for all opportunities in the data set (based on filter criteria) as depicted in the mock up. A good Opportunity to test with would be 22_NOV_40398 as it has the should have the same 75/25 split.
I think your calculation will still work, but how do you partition it by Opportunity? Thank you again for helping.

TableauCalculationExample.twbx 458.2 KB


3. Re: Percentage of other value calculation not of total.
Derrick Austin Mar 10, 2016 7:58 AM (in response to Josh Pyszkowski)Hey Josh,
You can add your dimensions to the LoD details to make it work with multiples.
It does look a bit funny when you have over 100% increase. I'd imagine a business rule of some sort should be implemented in those cases?
IF ATTR([Rollup Type]) = 'Current Stage'
THEN SUM([Rollup Amount])/MAX({FIXED [Opportunity], [Seller], [PastStatus] : SUM(IF [Rollup Type] = 'Past Stage' THEN [Rollup Amount] END)})
ELSE 1  (MAX({FIXED [Opportunity], [Seller], [PastStatus] : SUM(IF [Rollup Type] = 'Current Stage' THEN [Rollup Amount] END)}) / SUM([Rollup Amount]))
END

4. Re: Percentage of other value calculation not of total.
Josh Pyszkowski Mar 10, 2016 8:39 AM (in response to Derrick Austin)Nice! Yes I agree it looks funky and I can work on that. Sorry, but one last thing. Is there a way to AVG that calculation instead of AGG?
If you think about the image below. If these two opportunities each started out at 100K so 200k in total. They are currently worth 100K each so 200K total. So in total in their current state they are worth 100% of what they started out as.

5. Re: Percentage of other value calculation not of total.
Derrick Austin Mar 10, 2016 9:01 AM (in response to Josh Pyszkowski)It is already aggregated, but you can do a second LoD calc:
{FIXED [Rollup Type],[Opportunity], [Seller], [PastStatus] :
IF MAX([Rollup Type]) = 'Current Stage'
THEN SUM([Rollup Amount])/MAX({FIXED [Opportunity], [Seller], [PastStatus] : SUM(IF [Rollup Type] = 'Past Stage' THEN [Rollup Amount] END)})
ELSE 1  (MAX({FIXED [Opportunity], [Seller], [PastStatus] : SUM(IF [Rollup Type] = 'Current Stage' THEN [Rollup Amount] END)}) / SUM([Rollup Amount]))
END}

6. Re: Percentage of other value calculation not of total.
Josh Pyszkowski Mar 10, 2016 11:35 AM (in response to Derrick Austin)Derrick you have been a great help. I can certainly use what you have done for the detail portion of my reporting but for some reason it is just not visualizing correctly. In case I was vague in my explanation. The pie chart is to visualize the percentage of revenue that is still on the table sum(CurrentStage))/Sum(PastStage) vs the percentage that is not (sum(PastStage)  sum(CurrentStage))/Sum(PastStage) so for instance this should show 71.4% still on the table and 28.5% is not.
125,000/175,000 = 71.4% and (175,000125,000)/175,000 = 28.6%
Again sorry for all the back and forth and I am certainly going to look into the LoD calculations. I just dont quite understand it yet.
Thanks

TableauCalculationExample.twbx 805.3 KB


7. Re: Percentage of other value calculation not of total.
Derrick Austin Mar 10, 2016 11:42 AM (in response to Josh Pyszkowski)No worries  here is a rolled up version of the calc. Attached it updated in the workbook.
IF MAX([Rollup Type]) = 'Current Stage'
THEN SUM([Rollup Amount])/SUM({FIXED [Opportunity], [Seller], [PastStatus] : SUM(IF [Rollup Type] = 'Past Stage' THEN [Rollup Amount] END)})
ELSE 1  (SUM({FIXED [Opportunity], [Seller], [PastStatus] : SUM(IF [Rollup Type] = 'Current Stage' THEN [Rollup Amount] END)}) / SUM([Rollup Amount]))
END

TableauCalculationExample (4).twbx 804.8 KB


8. Re: Percentage of other value calculation not of total.
Josh Pyszkowski Mar 10, 2016 12:18 PM (in response to Derrick Austin)I think that does it. Thanks again Derrick! So basically the LoD is a way of pinpointing different data points to use in your calculation?
I noticed that the chart isn't Displaying the label to the 100% slice, only the 0% slice. How would I get the chart's labels to say 100% and 0%? Should I open up a new thread for that?

TableauCalculationExample.twbx 805.3 KB


9. Re: Percentage of other value calculation not of total.
Derrick Austin Mar 10, 2016 12:31 PM (in response to Josh Pyszkowski)Hum... That is indeed kind of goofy. I don't spend a lot of time building pies, but you would assume that it would say 100% instead of 0. You might indeed open a new thread on that to see if anyone else has a good fix. I played around with a few things and it seems pretty stuck.

10. Re: Percentage of other value calculation not of total.
Josh Pyszkowski Mar 10, 2016 12:33 PM (in response to Derrick Austin)will do thanks