topic Re: Pivot Table Cost Income ratio in Connectivity & Data Prep

Pivot Table Cost Income ratio

Sanju1 — Mon, 07 Jun 2021 20:15:41 GMT

Hi,

I am facing a problem to bring the Operating expense ratio (cost/Revenue) in a pivot table. I can easily achieve this by country using formula "sum({<AccountName={'Indirect'}>}FY)/sum({<AccountName={'Revenue'}>}FY)"

However, i do want to see the OpEx by function (eg., IT, Finance etc), table contains revenue by country and indirect cost split by country/function.

I am unable to get the OpEx ratio by the above formula in a pivot row/column view, it all shows null().

I can do alternate by applying the formula to individual function type, which means i will end up creating 20 separate measures.

Looking forward for a smart and easy solution. QVF file is attached.

Pivot viewFormula i am using

Thank you and best regards

Re: Pivot Table Cost Income ratio

GaryGiles — Mon, 07 Jun 2021 20:20:57 GMT

The following should give you the ratio of Indirect Function/Subfunction to Country Revenue, in the pivot, if that is what you are looking for.

sum({$<AccountName={'Indirect'}>} FY) / sum({$<AccountName={'Revenue'}>} Total <Country> FY)

Re: Pivot Table Cost Income ratio

Sanju1 — Mon, 07 Jun 2021 20:44:08 GMT

Hi Gary,

I have no words to explain, that's exactly what i was looking for. Thank you so much for your reply, I appreciate your help.

Best Regards

Re: Pivot Table Cost Income ratio

Sanju1 — Tue, 08 Jun 2021 11:15:22 GMT

Hi Gary,

The formula works great. I am facing an issue while filtering the columns, maybe you already to know how to avoid the filtering issue as well? When i filter by sub-function its shows me Zero.

Looking forward to hear from you.

Regards,

Umar Sherief

Re: Pivot Table Cost Income ratio

GaryGiles — Tue, 08 Jun 2021 12:08:48 GMT

You add the Function and Sub-Function fields to the set analysis so selections made in these fields are ignored for the Revenue calc like this:

sum({$<AccountName={'Indirect'}>} FY) / sum({$<AccountName={'Revenue'},Function=,SubFunction=>} Total <Country> FY)

But, since you have Nulls in your data and the data model is not optimal, you will see a Null value in your dimension values.

Re: Pivot Table Cost Income ratio

Sanju1 — Tue, 08 Jun 2021 12:17:30 GMT

Hi Gary,

This is wonderful, i tried those function, sub-function set parameters in the total's and in cost section. Didn't try it in the revenue side.
It works like a charm. Once again thank you for your support and appreciate your help.