topic Re: Divide all values by one value from the table in App Development

Divide all values by one value from the table

madnanansari — Mon, 13 Feb 2017 09:49:28 GMT

I have a Profit And Loss statement table like below:

PNL Head - Amount

Revenue - 500

Cost of sales - 200

Gross Profit - 300

Other Expense - 100

Net Income - 200

I would like to show this table on the screen; I would like to show a calculated column also which is the % age of PNL Head values with the total Revenue.

The final table should look like below:

PNL Head - Amount - %

Revenue - 500 - 100

Cost of Sales - 200 - 40

Gross Profit - 300 - 60

Other Expenses - 100 - 20

Net Income - 200 - 40

Pls let me know how to calculate the % column.

Re: Divide all values by one value from the table

kaushiknsolanki — Mon, 13 Feb 2017 09:57:49 GMT

HI,

you can use the expression as below.

Sum(Amount) / Sum({<PNL_HEAD = {"Revenue"}>}Amount)

Dimension will be PNL_Head

Regards,

Kaushik Solanki

Re: Divide all values by one value from the table

sunny_talwar — Mon, 13 Feb 2017 10:30:04 GMT

I think you missed the TOTAL Qualifier

Sum(Amount) / Sum(TOTAL {<PNL_HEAD = {"Revenue"}>}Amount)

Re: Divide all values by one value from the table

kaushiknsolanki — Mon, 13 Feb 2017 10:35:12 GMT

Hi Sunny,

I dont think so it would be required.

Let me test it though.

Regards,

Kaushik Solanki

Re: Divide all values by one value from the table

rahulpawarb — Mon, 13 Feb 2017 10:37:08 GMT

Hello Adnan,

Trust that you are doing great!

As a workaround please follow below steps:

1. Create a variable vRevenue with following definition:

=Sum({<[PNL Head] = {"Revenue"}>}Amount)

2. Create a Straight Table with [PNL Head] as dimension

3. Add Sum(Amount) as expression i.e. Amount

4. Add another expression % age wrt. Revenue with following definition:

Sum(Amount) / $(vRevenue)

Hope this will help.

Regards!

Rahul

Re: Divide all values by one value from the table

kaushiknsolanki — Mon, 13 Feb 2017 10:37:18 GMT

You Are right as usual.

It is required. Thanks for corrections.

Regards,

Kaushik Solanki

Re: Divide all values by one value from the table

sunny_talwar — Mon, 13 Feb 2017 10:37:33 GMT

I think when you use this

Sum({<PNL_HEAD = {"Revenue"}>}Amount)

It will put the revenue amount only on the PNL_HEAD row where it equals Revenue. To show it across all the rows, you will need to add TOTAL.

But do let me know how the testing goes. I will be curious to know.

Best,

Sunny

Re: Divide all values by one value from the table

jmdenny10 — Wed, 27 Mar 2019 00:25:15 GMT

I've got the same issue but across I have it for several months. Using total appears to total revenue for all months before dividing. Is there a way to total revenue for that column and divide all values in that column for it?

Re: Divide all values by one value from the table

SundarAnupindi — Fri, 17 Jun 2022 03:16:25 GMT

Thank you for the Article. I think this has provided us with the results that we need. Our Team is looking more into it.