Top N Brands

[Nikhilburhade]

I have created three calculated fields

1. Field 1 - N Dollar Monthly Share :- {Sum of Dollars} / sumOver({Sum of Dollars}, [Date])
2. Field 2 - N Unit monthly share :- {Sum of Units} / sumOver({Sum of Units}, [Date])
3. Field 3- Goal3
   ifelse(
   ${UnitDollarShare} = ‘Unit Share’,{N Unit monthly share },
   ${UnitDollarShare} = ‘Dollar Share’,{N Dollar Monthly Share},
   0
   )
   Where “UnitDollarShare” is parameter but i am not able to see either of these calculated fields in the select by option of brand filter ?
   
   

[Brett]

Hi @Nikhilburhade and welcome to the QuickSight community!
A couple questions with this:

• Are these calculated fields working on their own as expected? Normally when working with percentages, wouldn’t you want to swap the numerator with the denominator on those formulas?
• What are you trying to get your filter to accomplish?

Some functions do not work properly with top and bottom filters so you may need to incorporate Level Aware Calculations (LAC)

[Nikhilburhade]

Hello,

A) The calculated fields are functioning correctly, but when applying a brand filter using the filter list, the result shows 100% for the selected brand. This happens because the denominator is being affected by the filter and is not fixed. To address this, please guide How the denominator (e.g., total units for all brands) remains constant and unaffected by filters.
Note - When we cross check the percentage numbers without applying Brand filter then they are fine.

• Below are the field formulas for your reference -

• Field 1 - N Dollar Monthly Share :- {Sum of Dollars}/ nullIf(sumOver({Sum of Dollars}, [truncDate(‘MM’, Date)]), 0)

• Field 2 - N Unit monthly share :- ({Sum of Units}) / nullIf(sumOver({Sum of Units}, [truncDate(‘MM’, Date)]), 0)

• Field 3- Goal 3 Unit & Dollar Monthly Share

ifelse(
${UnitDollarShare} = ‘Unit Share’, {Goal 3 Unit Monthly Share },
${UnitDollarShare} = ‘Dollar Share’, {Goal 3 Dollar Monthly Share},
0
)

Where “UnitDollarShare” is parameter that toggles between Unit share and Dollar Share

B) I am trying to fetch the Top N brands in a chart while ensuring that their percentage values remain consistent, regardless of how many brands are displayed. For example, if Ryobi’s market share in December is 29.8%, it should stay 29.8% even if only Ryobi is shown in the chart. Please advise on how to implement this.

• Attaching Reference image for Issue A in this post and
• Please refer to the image attached in previous post for issue B

Thank You.

[Brett]

Hi @Nikhilburhade,
In regards to getting your percentage to stay the same, you’ll want to include ‘PRE_AGG’ in your calculation to make sure that it’s unaffected. There is additional information on how to utilize this in the article I linked above.
For your situation of trying to find the top N brands, have you tried setting up a rank or denserank calculation based on the field aggregations that you’d like to be included in setting that ranking? Then you can filter based off that ranking field.

[Nikhilburhade]

Hey @Brett
Thank you so much for the guidance.

Thanks,
Nikhil.