9 Replies Latest reply on Jun 14, 2018 3:15 PM by Bryce Larsen

# Need help with % of total calculated field

Hi,

Im trying to create a view that shows the % of total revenue by country & app but am having trouble due to the way the countries are represented in the source data. The data shows revenue for each app for the top countries as well as a 'WW' field that is total revenue for that particular app. Because of this, when I run the % of total calculation down the table it includes WW revenue in the % of total calculation. I am trying to show revenue for each country/app divided by the total (WW) for that app. Anybody got a method to do this? Thanks!

• ###### 1. Re: Need help with % of total calculated field

Hi

If I understand correctly you have a dataset with the total included in the disaggregate date

the easiest approach is to take the total out - here are 2 ways

Place country on the filter shelf

open the country pill by right clicking and Select the WW then select Exclude

Go back to the filter and right click and select Add to context

the alternate way is to create a nue dimension - "New Country" with a formula like    if [Country] <>      'WW' then Country end

and use the New country in your viz - you will need to exclude the null

then either way you can use the table calcualtions

the % of total calculated sown

will return this

Jim

If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

• ###### 2. Re: Need help with % of total calculated field

Hi Dane,

Adding to what Jim said, easiest way to do this is to exclude WW. However, if you want to include this, you can do the following:

SUM([Revenue])

/

IF [Country]<>"WW" THEN

{exclude [Country]: SUM(

IF [Country]<>"WW" THEN [Revenue] END)}

ELSE SUM([Revenue])

END

I think this should work and show the accurate % for each location while showing 100% for the WW row if you need to keep this in the view.

Best,

Bryce

• ###### 3. Re: Need help with % of total calculated field

Thanks for this, Jim. I tried your 1st method and although its closer, I'm still not getting the correct output. In order for me to get the correct market share % for each country, I need the revenue total for that country divided by the WW total for the same product_name. The countries shown do not represent the entirety of the the revenue--the WW revenue # for each product_name includes revenue generated in a bunch of other geos not broken out in the data set. Any way to get around this?

• ###### 4. Re: Need help with % of total calculated field

Hi Bryce,

Just tried this method but got a "cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions" error that I was unable to properly troubleshoot. Any tips?

• ###### 5. Re: Need help with % of total calculated field

{include [Country]: SUM([Revenue])}

/

IF [Country]<>"WW" THEN

{exclude [Country]: SUM(

IF [Country]<>"WW" THEN [Revenue] END)}

ELSE {exclude [Country]: SUM(

IF [Country]="WW" THEN [Revenue] END)}

END

• ###### 6. Re: Need help with % of total calculated field

This is giving me the same output as Jim's solution above. Instead of dividing each country's revenue total against the sum of all countries revenue, I need each country's revenue total divided by WW for each product_name.

• ###### 7. Re: Need help with % of total calculated field

Ah I think I understand. How’s this?

{include [Country]: SUM([Revenue])}

/

{exclude [Country]: SUM(

IF [Country]="WW" THEN [Revenue] END)}

1 of 1 people found this helpful
• ###### 8. Re: Need help with % of total calculated field

Boom! That's it. Thanks so much for the help Bryce Larsen

• ###### 9. Re: Need help with % of total calculated field

Happy to help! dane tahbaz