10 Replies Latest reply on Mar 10, 2016 12:33 PM by Josh Pyszkowski

# Percentage of other value calculation not of total.

Hi All,

I am trying to do what looks like it should be one of the native table calculations but none are giving me what I need. The used case is I have  revenue of a sale that was in a past stage and revenue in its current stage of the sales process. Essentially I need current stage revenue/past stage revenue...but also the remaining percentage.  Attached is an example workbook.

I appreciate the help.

Thanks

• ###### 1. Re: Percentage of other value calculation not of total.

Hey Josh,

I've attached an example of how to do this with LoD calculations.

Hope this helps!

- Derrick

• ###### 2. Re: Percentage of other value calculation not of total.

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.

• ###### 3. Re: Percentage of other value calculation not of total.

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.

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.

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.

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,000-125,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

• ###### 7. Re: Percentage of other value calculation not of total.

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

• ###### 8. Re: Percentage of other value calculation not of total.

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?

• ###### 9. Re: Percentage of other value calculation not of total.

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.

will do thanks