Skip to Content
1

Is there a way where we can show values as percentage of total for a given measure?

Jan 29, 2017 at 05:38 PM

217

avatar image

Hi,

Consider I have a Table view as below.

Can someone suggest how can we get the Totals column as percentage of row totals as an additional column in the end?

Regards,

Kiran Shenvi

basic-tables.jpg (18.1 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
John Leggio
Jan 30, 2017 at 10:20 PM
0

Hi Kiran,

As Petar mentioned, you can use a calculated measure (create in account dimension) referencing a restricted measure (create in account dimension) in your story. This works for Planning and Analytical models.

Here is how:

  • TOTAL_ALL2 is a restricted Measure with the following options (Constant Selection checked / City dimension with values of Brazil, Canada, India chosen ... note that my city dimension has a hierarchy and those are the parent members, in your case just select all the cities in your dimension).
  • % of Total is a Calculated measure referencing the Value account / the restricted measure defined above:

    Formula = [Value_XYZ] / [@TOTAL_ALL2]

Don't forget to format the "% of Total" calculated measure to "Percentage":


Show 4 Share
10 |10000 characters needed characters left characters exceeded

Thankyou so much John... This works perfect!!!
Only care to be taken is that the values of the dimension are not changing very often or remain static.

1

Hi Kiran,

A good workaround for that is creating a hierarchy in your dimension and then creating a TOTAL dimension member. Then reference the TOTAL dimension member in the Restricted Measure dimensions Values field:

When you add new city or country, you won't need to change the the restricted measure.

It works great!!!

1

Hi John,

I need your help again!

I was trying to work with Restricted measure with LIVE connection. But it was not behaving like total.

In that UI, the checkbox for Constant Selection is not enabled and hence it cannot sum up.

Can you suggest any workaround for this?

Regards,

Kiran

0

wow, nicely done !

1
Petar Daniel Jan 30, 2017 at 05:29 PM
2

In my opinion, you should be able to add a calculation for the specific Total defined in the grid for your account/measure. Then add a second calculation which uses the account/measure and is divided by the previously defined calculation of total. This method assumes a planning model is defined and would build the calculations in the Story, not by the account dimension. Hope this helps.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Hi Peter,

Thanks for your response. Looking at the configuration of environment at my client side.

We don't have license to Planning model so our models are analytic in nature. Due to this I can't use the cell reference which you correctly advised.

Also, we are required to use Live Connection to Hana Models built using Calculative Views. Over here again the limitation is that Calculation Editor doesn't support Aggregation Type for LIVE Connections. Due to this I am not able to implement the % formula. :-(

Hope we getting some more functions in future releases.

Regards,

Kiran Shenvi

1
Tammy Powlas
Jan 30, 2017 at 12:16 PM
1

You might try something with formulas - see this YouTube https://www.youtube.com/watch?v=lI4LQM4Y4z4

But a function like percentage of share may be nice to have, like Analysis Office - in which case I recommend submitting an idea to Idea Place at https://ideas.sap.com/ct/ct_list.bix?c=67F73D87-9FB4-4410-A955-352068451176 - I couldn't easily locate an existing idea.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thanks.

Yes this is really a must and nice feature to have.

I have raised the idea for the same. Most in the community can now upvote it.

https://ideas.sap.com/D40286

Regards,

Kiran Shenvi

1