Percent of Total Calculation for String Data in Pivot Table

Hi All

I’m trying to create a column in the below table showing the percentage of each row over the total shown.

The ‘test’ value is a calculated field - ‘percentOfTotal(count(fraudmethodchild),[{Impact Framework - Other}])’ - with Impact Framework - Other being a nested calculated field - ‘distinct_countIf(caseid,contains({Impact Framework},“Other”))’

Can anyone suggest what I’m doing wrong?

Thanks

Hi @hopeah ,

Welcome back to the community!!

I see you want to calculate percent of total but in the partition you have mentioned the measure field itself.

Are you trying to calculate 5/73 ~ 6.8% instead of 51.85%?
If so, what is the calculation for the count field and what is the dimension used here?

Thanks,
Prantika

Hi @prantika_sinha

That’s right, the count field calculation is distinct_countIf(caseid,{Impact Framework}=‘Other’)

I’m sorry, but I don’t know what the dimension is!

If this is the use case, then we can calculate the denominator and then write another calculated field to find the distribution %.

Step 1: calculate Numerator (this is already done by you)
Step 2: calculate denominator - Total case with impact framework other
totalcase = distinctcountover(ifelse(impact framework=‘Other’,caseid, null),,pre_Agg)
Step 3: find ratio from numerator and denominator
%total = count / avg(totalcase)

Please fix the syntax and cases as needed.

Hi @prantika_sinha

Thanks for your help with this. I’ve used the following calculated fields:

  1. totalcase - distinctCountOver(ifelse({Impact Framework}=‘Other’,caseid,NULL),[{Impact Framework}],PRE_AGG)

  2. %total - count({Impact Framework})/avg(totalcase)

But I keep getting the following:

It looks like the %total is calculating totalcase/count, rather than count/sum of impact framework = ‘Other’.

I see you have added impact framework as partition while calculating totalcase. can you remove that instead and keep the partition blank?

I’ve tried the calculation both ways, but the pivot table returns the same results regardless.

I’ve had to export the underlying data to Excel and created a new database, which seems to be a way around the problem.