Percent of Total Calculation for String Data in Pivot Table

Question & Answer
, , ,
1

Hi All

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

image
image479×313 17.6 KB

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

2

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

3

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!

4

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.

5

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:

image
image577×295 24.9 KB

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

6

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

7

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.