topic Re: How to get sum of Top10 values of a measure. in DAX Commands and Tips

How to get sum of Top10 values of a measure.

Anonymous — Tue, 17 Nov 2020 14:35:36 GMT

Hi Experts,

I want to show sum of Top10 of Growth Contribution. Growth Contribution Measure is (Growth Brand/Sum of All Positive Grand Growth).

Growth Total (Sum of All Positive Growth Brand) = CALCULATE([Growth Brand],ALLSELECTED('Product'))

Issue number one above measure is changing when select filtering for Top 10 Values it should be same.

Other issue i want sum of measure Growth contribution for Top 10 Brands.

Growth Brand =

VAR ShareLY = ([Commercial Sales Week Volume LY]/CALCULATE([Commercial Sales Week Volume LY],REMOVEFILTERS('Product'[Brand Family],'Product'[Brand],'Product'[Consumer Product],'Product'[Brewer Parent])))*100

VAR Sharechg = [Share Actual M] - ShareLY

Return

IF(HASONEVALUE('Product'[Brand]),if(Sharechg > 0,sharechg,0),Sharechg)

Not sure how to get the sum of top 10 Brand Growth Contribution.

Thanks for support in advance.

Re: How to get sum of Top10 values of a measure.

amitchandak — Tue, 17 Nov 2020 15:30:05 GMT

Anonymous , you can TOPN.

refer video if that can help - https://www.youtube.com/watch?v=QIVEFp-QiOk

Re: How to get sum of Top10 values of a measure.

Anonymous — Tue, 17 Nov 2020 16:31:17 GMT

amitchandak Thanks for your reply.

I have already done almost similar things as per your video, but issue is that Growth Contribution should be only for the positive value i.e. Sharechg. For example below 24 brands but it will change based on filter. In visual filter i have already put filter for sharechg > 0 so Growth Total is dynamic.

2.83
2.7
1.6
1.33
0.99
0.99
0.77
0.67
0.35
0.32
0.32
0.24
0.22
0.2
0.17
0.15
0.13
0.09
0.08
0.08
0.05
0.04
0.03
0.01

Re: How to get sum of Top10 values of a measure.

Sam7 — Tue, 07 Jun 2022 15:21:00 GMT

Hi, firstly I have found your YouTube videos, they are great

Top10 amount=

CALCULATE(sum('TABLE1'[SALES]),TOPN (10, ALL('TABLE1'[PRODUCT]),sum('TABLE1'[SALES]),DESC))

I tried this, but I keep getting the total, not the top 10 total, what am I doing wrong? Thanks