How to work out the percentage of a row

Ajay Sonoo shared this question 6 months ago
Answered

Hi There,

I've created a table to work out the percentage between SUM noOfdDirectPriceCheaper and Sum noOfAppearinSearch using the following calculation using the Simple formula type: SUM ( noOfDirectPriceCheaper ) / SUM ( noOfAppearInSearch ) * 100.

However, the calculation is providing the results for the columns only, instead on a row-by-row basis (see file - percentage total is based on the sum of the two columns).

Can you advise on how to apply the calculation to each row, with the result showing under %BestPrice

Thanks,

Ajay

Comments (1)

photo
1

Hi Ajay,


It sounds like you are performing a calculation on the two aggregated columns, rather than performing the calculation row by row.


Try

noOfDirectPriceCheaper / noOfAppearInSearch * 100
 and let me know how you go. 


Kind regards,

Simon

photo
1

Hi Simon,


Thanks for your reply.

I have tried your suggestion, however that doesn't seem to work either. Where there is a clear difference between noOfDirectPriceCheaper and noOfAppearInSearch,, the percentage figure shows as 0.00 (see file).

Thanks,

Ajay

photo
1

Hi Ajay,


I would say that there is something wrong with your data in that case, or potentially the formatting of the that might be preventing you from viewing the calculations correctly. Could you please show me the screenshots of the column format page for each of the fields you have created?


Kind regards,

Simon

photo
1

Hi Simon,

Please find the attached screenshots as requested.

Thanks,

Ajay

photo
1

Hi Ajay,


Would you be available for a screenshare sometime later today AEDT? It will be easier to look through your report an understand what is happening. Let me know a time that suits and I will schedule a meeting.


Kind regards,

Simon

photo
1

Hi Simon,

Are you able to do it in half an hr - 11AM AEDT ? It will be Midnight in the UK.

Thanks,

Ajay

photo
1

Hi Ajay,


Please join me at 11 AM AEST.


Simon Karumbi is inviting you to a scheduled RingCentral meeting.


Topic: Simon Karumbi's Personal Meeting Room


Join from PC, Mac, Linux, iOS or Android: https://meetings.ringcentral.com/j/2391465002


Or iPhone one-tap :

GB: +442038754507,,2391465002#

Or Telephone:

Dial(for higher quality, dial a number based on your current location):

GB: +442038754507

Meeting ID: 239 146 5002

International numbers available: https://meetings.ringcentral.com/teleconference

photo
1

Hi Ajay,


Thanks for joining me on the call.


To go over what we discussed, your values in the Data Source are Integer data types, which are unable to be used correctly in decimal calculations. For this reason, you will need to convert/cast these values in your Reports or your View in order to get Indexes, Ratios, and Percentages when required. Using a SQL generated Calculated Field, the calculation we were successful using was:


CAST(variableA as FLOAT)/CAST(variableB as FLOAT) *100

"*100" is optional, as you are able to format the value as a Percentage from within the Column Formatting options. I would suggest this, as it will be easier to create reports and spot mistakes and permit you to create Percentage Bars and other


The solution to our problem aggregating the calculated total can be found in the screenshot below. Please use 'Calculated Total' for the Total Value percentage that you are looking for.


/88b9e1677d1e98e8748102cdebbd06af


Let me know how you go.


Kind regards,

Simon

photo
1

Hi Simon,

Many Thanks for your help in this matter!

All the best,

Ajay

photo
1

Hi Ajay,


That's not a problem.


Let me know if this has been resolved and I will go ahead and mark this ticket as completed.


Thanks,

Simon

photo
1

Hi Simon,

Apologies for the delay. The issue has been resolved. Thanks again for your help in this matter.

Regards,

Ajay

photo
1

Hi Ajay,


Not a problem, I'm glad I could help.


I'm going to go ahead and mark this ticket as completed.


Kind regards,

Simon

photo