% of grand total only against top category (but showing lower level categories)

[redalert787]

Hello, 

I am trying to solve an problem where I'd like to create a measure that shows the % of grand total for our top 10 models names of products against their top level category. The example here is I'd like to show the top 10 products in our "apparel & accessories" division but I'd like to also show which sub-category those model names belong to (For example, if they are in our sub-category of Ski or running). The issue I'm facing is that the calculation works fine when I only show the model name or model name with the top category (Apparel & accessories) but the moment I bring in the sub-category (ski, running etc) the measure calculates the % against the total of the sub-category and not the top category. 

here is a screen shot of the measure I created: 

Here is a screenshot of the table without the sub-category and the % working okay:

And here is a snapshot of the sub-category included and the measure not working properly anymore:

I think I am writing the equation incorrectly, so if someone can help I would appreciate it. 

P.S - if anyone has any suggestions on how to visualize this better than just a boring table...I'm very open to suggestions 🙂 

[v-zhangti · Accepted solution]

Hi, @redalert787 

You can try the following methods.

Top 10 = 
CALCULATE(SUM('Table'[Invoiced Sales]),
          TOPN(10,allselected('Table'),[Invoiced Sales],DESC), VALUES('Table'[Model name]))

% of total sales = 
DIVIDE([Top 10], CALCULATE(SUM('Table'[Invoiced Sales]),ALLSELECTED()))

The presence of Famliy does not affect the sorting.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more.

[amitchandak]

@redalert787 , Try measures like

Top 10= calculate([Invoice Sales], TOPN(10,allselected(Product[Product]),[Invoice Sales],DESC), values(Product[Product]))

% of total sales = divide([Top 10], [Invoice Sales])

or
% of total sales = divide([Top 10], calculate([Invoice Sales],allselected() ))

[redalert787]

Hi @amitchandak ,

Thanks for your response. I have a question regarding the top 10 measure: 

What do you mean by "DESC" in the measure? 

[amitchandak]

@redalert787 , desc for descending sort