Ranking changes the calculated percentage

[Former Member]

I want to show the share of sold products. Adding Calculation > Percentage for the measure works nicely, however, if I add a ranking the percentage will be based only on the ranked volume instead of the total. Attached you'll find two screenshots based on the dummy sales data. The revenue share of the BL Blades increases from 22 % to 38 % after applying a Top 3 ranking. Is it a bug or a feature? In my option this is absolutely wrong (otherwise I'm adding a new entry to the ideas place). Or is there a work around?

Two remarks:

1. In Tableau the calculated percentages are based on the total sum regardless of the ranking

2. There is no way to format the calculated percentages in Lumira. The percentage sign and decimal figures are not shown. Additionally, the labels on the axis is not nice: 10,000 instead of 10%

Thanks,

Karsten

[Daniel_Davis · Accepted solution]

Karsten,

I don't have a fix but I do have a workaround (ish). Assume your dataset is something like

Name        Revenue
Daniel       100
Karsten     150
Peter         250
Mark          200
Paul          300
Jag            225

The % calculation in Lumira works on the Ranked data not the underlying data so when you add the top 3 rank you then see the % as the top 3 totalling 100%. I think that's incorrect or at least should be an option of 'From All Values' or 'From Ranked Data Only' but it is what it is.

So you get something like this before the ranking:

and once you add the ranking you get:

and as you can see the top 3 values now total 100% whereas before they totalled 62%

However In my dataset I add a 3rd column that is the percentage so I then get a dataset of:

Name         Revenue    (%)
Daniel         100           0.08
Karsten       150           0.12
Peter           250           0.20
Mark           200            0.16
Paul            300           0.24
Jag             225            0.18

Note the formatting - not a % until it's inside Lumira.

In Lumira you now have a measure called (%) that you need to change the aggregation to None. Once inside Lumira you can then plot the (%) Dimension rather than the Percentage: Value calculation giving...

Before the ranking

and finally once you add the Top 3 Ranking

You can now see that the 3 (%) values are the original % rather than the total of the ranked values. Not perfect but works.

Enjoy

Daniel

[Former Member]

Hi Daniel,

Thanks for your nice workaround. I'll give it a try. But it won't work with drill downs because the underlying data for the % calculation should change in these cases. Or do you have a workaround for that, too? A sumif formula would be really nice.

You mentioned a possible fix so that the % calculation could have options like 'From All Values' or 'From Ranked Data Only'. You work for SAP, right? Are you able to add this fix to an internal bug tracking tool or shall I submit an idea? I would also add an option 'From Filtered Data Only' for the drill downs.

Best regards,

Karsten

[Former Member]

Hi Sharon and Tammy,

May I ask again whether the percentage calculation based on the visible sum (instead of the total) is a bug or feature? I don't want to start the development of a custom extension if this will be fixed by your colleagues.

Best regards,

Karsten

[TammyPowlas]

Hi Karsten - Since I don't work for SAP, I'll defer to Sharon or someone else at SAP.

[TammyPowlas]

Hi Karsten,

You can format the percentages as follows:

Note my percentage field name is test - click the gear icon and select "Display Formatting"

Then select Percentage as shown above

In the ranking, I think mine worked - I selected the test as the item to rank -see below:

[Former Member]

Hi Tammy,

Thanks for your fast reply. Unfortunately, the display formatting affects the base value and the calculated percentage. Even worse: the calculated percentage is also multiplied with 100 even though it's already a percentage. Please have a look:

Best regards,

Karsten

[TammyPowlas]

OK, I think I follow this now.

I believe you are right; there is no real formatting of the percentage when you do Add Calculation > Percentage

There is a similar idea on Idea Place Cumulative percentage in running measure : View Idea

[som]

Even worse: the calculated percentage is also multiplied with 100 even though it's already a percentage. >>  This actually threw me off too at first but I have tried it in Excel and the behavior was the same.