Percent rank of all, but only show some

#1

I have a situation where I have data for 300 brands. I need to calculate each brand’s score as a percentrank out of all of them and display it in a chart. That is simple to do - it’s a built-in formula for a calculated measure.

My issue is that I need to be able to only show some brands in the chart - the viewer can choose any number or combination of brands from a filter, and these will be the only ones displayed.

How can I filter to show only a few brands, but still calculate a percentrank out of all of them? Is this automatically built in, or do I need to do something clever, and if so what?

#2

My gut says you’ll have to do that on the back end, i.e. imagine a CTE clause (likely with windowing to separate measure-related fields from filter fields), that calculates said percent rank, followed by a second clause that applies the requisite filters.

Editing this in case someone can point out a better way. As a general rule of thumb, I find that pre-filter calculations are best done on the back end, whereas post-filter calculations can only be done in Dundas. I would love to be corrected on this, but in my mind, this would require windowing on the Dundas side, which I have not yet figured out how to do. If anyone has figured this out, I would love to hear from you!

#3

Sorry for a 2nd response to this, David, but another thought did occur to me. If no one else can tell us how to achieve this in Dundas and you DON’T want to touch the back end, you can actually achieve it on the Dundas side by implementing the CTE/windowing solution using the Manual Select option in a Dundas cube.

#4

Hi David,

Since you’re into a pre-aggregation problem (from how i’m reading this) i’d go with the calculated element approach. To allow the user to choose the brands, add a filter before your calculation is performed. This way you’re still calculating for the whole data set but changing from user to user.

Basically:
image

You could probably also do this with a chain for formula visualizations but i prefer the data cube approach typically.

#5

Jeff,

Hopefully that answer’s David’s question, but I’m still a bit confused. Say you have 3 dimension values ( A, B, and C) and 1 measure (X).

Say you want a % breakdown by A only, but later, in your dashboard, you want to let users by filter by B and/or C.

What is the Calculated Element formula that lets you calculate a % by A only, i.e. before any filters have been applied.

Wayne

#6

Well i assume the percentage calculation was only for the subset of the data, not the data set as a whole. If you needed it for everything, you’d probably have what i described above to calculate one field and then join against the same table (one filtered, one not)

#7

My vote would be do this at the query level.
either the actual Query you put in the data cube, or use the Calculated element like Jeff said.
I think he had a type in that post. at least it was confusing to me.
I pretty sure that is you use a calculated element to set a value for a row, that it not going to change based on a metric set filter. It is the value for that row (ie Brand) so If you make a calculated element to give you the rank, then filter in the metric set you will get its rank based off the entire set not filtered result.
If I am wrong please tell me, and also then your only option is doing it at the SQL statement level.

#8

To clarify what I need - I’m still not 100% sure what the best approach is.

I need to calculate a measure - let’s call it ‘brand awareness’ as a percentrank. So each of the 300 brands has a ‘brand awareness’ score, and each of the brands will have a percentrank for that score out of all the brands. Take all 300 brands’ ‘brand awareness’ scores, and create a new measure for each of them which is the ‘brand awareness percentrank’ calculated from their score.

Now, going to the dashboard - I need to have a chart which only shows one or two of the brands. I need to show the brands that the user has chosen - let’s say ‘footwear brands’, but instead of showing the actual ‘brand awareness score’, the scores shown for those brands need to be the ‘brand awareness percentrank’ score for those brands, calculated out of all 300.

For that, I think that doing it at the cube level will work. Calculate a new ‘brand awareness percentrank’ measure which is all the scores for all the brands in the cube out of all the other brands, and then filter on the dashboard level to only show the values for the brands that I want.

I have the scores at a respondent level - meaning I need to aggregate them first. I guess I can use an aggregation transform to add up the individual answers to make a score and then feed that into the percentrank.

Next issue is, I may need to show the ‘brand awareness’ score for only the people in London. I will need to have a region filter which will filter all the scores for all the brands before it feeds into the percentrank calculation. For that I think Jeff’s solution will work - connect the filter in the datacube before the percentrank calculation. This can be public and so accessible to the viewer on the dashboard. This filter will change all the percentrank numbers for all the brands, and then my dashboard filter will only show the values for the brands that I need.

Make sense?

#9

My head spun around only twice :woozy_face: so yes i get it.

#10

Yes, got it. So, given your clarification and Jeff’s follow-up, it appears that an aggregate transform for the overall percentrank (or the summed data) joined back into the full dataset will do it.

…unless you are not storing the cube in-memory or on-disk, in which case the CTE/windowing solution might give you better perfornance.

Great subject, guys…it has now given me an alternative to using SQL for this type of problem.

#11

Thanks all - now I just need to do it :slight_smile:

Data comes in as Excel file, and although I would like to warehouse it that won’t be possible if filtering in the cube