How to get a percentage only on a selected fields (not null cases)

[aarnkalle]

Hi

With reference to above subject, I am trying below formula in expressions but getting "Error in Expression".

    (Sum(if(Levels = 'Level*', (Gross),'NA'))) / (sum((if(Levels = 'Level*', total (Gross),'NA'))))

Please see the above and help me to identify where I am making mistake.

Thanks in advance.

Abhijit ARANKALLE.

[Anil_Babu_Samineni · Accepted solution]

Perhaps this?

Alt(Sum({<Levels = {'Level*'}>} Gross)/Sum({<Levels = {'Level*'}>} TOTAL Gross), 'NA')

Or, For me this is suffice

Alt(Sum({<Levels = {'Level*'}>} Gross)/Sum(TOTAL Gross), 'NA')

[aarnkalle]

Hi

Thanks a lot for your response. It working fine in total but when I am adding quarter fields in pivot - column, then I am getting incorrect results by quarter.

Thanks and Regards,

Abhijit ARANKALLE.

[aarnkalle]

Only when there is only one dimension as Level. When you have a Quarter or Month dimension in column, then results are not perfect.

[sunny_talwar]

I see you already have a thread... would you be able to provide a sample with the issue and what you expect to see as the output from the sample provided?

[aarnkalle]

Hi

Please see attached. The red one is current result and green colour is I am expecting.

Thanks and regards,

Abhijit ARANKALLE.

[sunny_talwar]

Try this

Sum(Gross)/Sum(TOTAL <EncDate.YearQuarter> Gross)

[aarnkalle]

Hi

I tried this but it is taking percentage for NULL values also.

[sunny_talwar]

percentages for NULL values? really? Can you share a screenshot to show what you mean?

[aarnkalle]

Hi

Please see attached file for details.

Thanks and Regards,

Abhijit ARANKALLE.