Is there a way we can use running % and use it as a dimension to categorise the products.
I’m looking to categorise products which contributes 70% of the revenue as ‘A’, < 90% as ‘B’ and remaining as ‘C’. Any help would be appreciated. Or is there any other way of achieving it?
@Jesse could you please lend some help here?
Hmm this might be a little tricky.
For instance, why would some products be in category A vs B if they both contributed revenue?
For instance if one contributed 10% or revenue and the other contributed 20%, they both can be part of A, B & C right?
Is there a way you sort them maybe?
Or am I looking at this wrong?
I would think you’d just want to do an ifelse() setting the various thresholds. For example:
ifelse(
{revenue_perc} >= .9, ‘A’,
{revenue_perc} <.9 AND >=.8, ‘B’, ‘C’
)
Thank you for your response. Sorry if my question was not clear. we are trying to look at the products which are contributing to 70%(category A) of the revenue like we do on a Pareto and then see how many products falls into this category. I tried it using a running % of the their contribution which adds upto 70% as category A, but I’m not able the field as a dimension.
In the image above, the green cells add upto 70% which is my A, 70-90% will be B and rest as C. Hope I’m clear this time.
Hi Todd, Thank you for your response. I’m looking at a cumulative contribution of all the products which adds upto 70% of the revenue. If I look at just the revenue contribution of each product, all of my products would be categorised as A as the contribution will always less than 70%.
If it’s how you have it in your photo then it is sorted based on the sale amount.
You can do this.
running_sum_calculation = runningSum(percentOfTotal(sum({sales})),[{sales} DESC])
group_running_sum = ifelse({running_sum_calculation }>.3,‘A’,
{running_sum_calculation }>.1,‘B’,‘C’)
One thing to note is that you will need to reference the sales in any of your visuals. If you don’t want to do this, then you will need a way to transfer this logic to SQL.
Hope this helps.
Thanks Max. I’m able to get the group_running_sum in the table. As running sum is a table calc it needs the product in the view to calculate running sum. My next step is to count the products based on the group_running_sum. Do you think there is any way out or I should move the logic to sql as you suggested.
You can try and do a count over this value and partition it by group although I don’t know if that will throw errors.
I would suggest moving it to SQL because you can then do the counts as well as use it in a visual without relying on the sales field.
Thanks max, I was able to find a solution as of now, did a running count of ranks and then took a max for each category and then filtered only for first row to mimic as a aggregated table.
1 Like