Contribution percentage by top n filter

Question & Answer
,
1

Hi Everyone,

I have a requirement to find the contribution percentage of sales for all the weeks of a year based on top n parameter. For example, if top n is set to 5, then the pivot table should display contribution percentage of that value for top 5 regions.(columns will have report year ).

Numerator : sum of sales partitioned by region,year,week
Denominator: sum of sales partitioned by only year and week.

Both numerator and denominator should filter by week_category parameter. For example, if week_category_parameter = +6 weeks’ , then the numerator’s calculated field should use the condition.
Eg: ifelse(week_category_parameter=‘+6 weeks’,sumif(sales,week_category=‘+6 weeks’),week_category_parameter=‘+7 weeks’,sumif(sales,week_category=‘+7 weeks’,NULL) (*this calculated field should be partitioned by region,year,week)
The same applies for denominator by filtering the week_category_parameter, but the denominator should be partitioned by year,week.

Then , divide numerator by denominator which is a percent value (contribution %) and this should filter by top n region parameter.

I’m not able to create calculation for the above scenario.For testing purpose,I tried to test if numerator and denominator are correct in a table first. I am able to get the numerator value correct by adding year,week,region,numerator. But in the same table, when I add denominator, I’m getting the same value as numerator because there is region field added(but denominator should give value based on only year and week).

Kindly help me with this requirement.

2

Hi @Kripa_Magesh,
With the number of calculations and parameters being utilized for this case, the best way for us to assist further would be for you to upload a copy of your analysis with sample data to QuickSight Arena if able to. That way we can test out some possible solutions!

If unable to, could you share screenshots of the calculated fields you’ve put together for the numerator and denominator as well as the parameters being utilized for this case?

1 Like
3

Hi @Brett ,

Thanks for the reply.

I used PRE_FILTER for denominator and got the right answer. I got the solution.

2 Likes
4

Hi everyone,

I have a requirement to find contribution % of the above calculation filtering by top n parameter. Also, I need to add a column before this calculation in a pivot table which gives top n for the filtered regions and others for the regions that are not in top n.

Eg: In a pivot table, 1st column will be “ top n and others”, 2nd will be regions, and then report week wise contribution percentage.

I used rank function like if rank of the contribution % < top n then give me top n like top 2 else give me others.

This is not working for me. When I use this in pivot table it’s throwing error.

Kindly help me with this