Top N with grand percentage of column total in line graph

[lostinpowerbi]

I am new to PowerBI, and I have spent hours going through these forums to try to answer my questions - to no avail.

I would like to make a line graph that shows the % of sales per month, where I visually show the sales of the top 5 products.

I have about 2000 products in my data, and I want the % to reflect the performance of the top 5 products also taking into account the "other"1995 products divided by month.

Issues that I have encountered

1) The grand percentage total is from the entire data, and not broken down per month. Even if you see in a matrix table that this is the case, it loses the data when you transform it to a line graph

2) I tried to use the rank X function, to address this problem (and follow the advise presented on Youtube (https://www.youtube.com/watch?v=HJdVfYkfhmE), however, I get an error and I cannot explain why

Step 1: _TOTAL SALES = SUM('data (2)'[sales])

Step 2: _SalesRank = RANKX(ALL('data (2)'[Product]),[_TOTAL SALES],,0)

Step 3: _Top 5 Product Sales = IF([_SalesRank]<=5,[_TOTAL SALES],BLANK())

Error message: The syntax for '[_TOTAL SALES]' is incorrect. (DAX(IF([_SalesRank]<=5.[_TOTAL SALES],BLANK()))).

Generally, the steps outlined above should present the top 5 products, and leave out all other brands while still maintainng the total with the other brands considered. I do not understand why the error occured.

I am really lost and I appreciate any help that you can give in a detailed manner (as I am not familiar with all the terms and possible calculations).

[Ashish_Mathur]

Hi,

Do not create the third measure.  Drag the second measure to the Filter section and apply a criteria of <=5 there.

[lostinpowerbi]

Dear Ashish,

If I skip step 3, and apply the criteria of <=5 in the filter, the total is recalculated to only the 5 products. I need to have the grand total, but only show the top 5. Also, any help on showing the percentage per month would be great!

[Ashish_Mathur]

Hi,

Try this measure to compute the % of sales earned from Top 5 customers to total sales

=SUMX(TOPN(5,VALUES('data (2)'[Product]),[_TOTAL SALES]),[_TOTAL SALES])/[_TOTAL SALES]

Does this work?

[ruks83]

Hi Ashish,

I wasnt able to calculate top N % using the formula. Entity% is calculated measure. Can you please help

Entity    Entity%

test1     48%

test2     22%

test3      10%

test4       2.3%

test5       1.1%

test6       0.9% ....

Im using 

TopN% = SUMX(TOPN(5,VALUES(data[Entity]),[Entity%]),[Entity%])/[Entity%]

[Ashish_Mathur]

Hi,

Does the numerator give you the correct answer?

=SUMX(TOPN(5,VALUES(data[Entity]),[Entity%]),[Entity%])

If not, then share some data and show the expected result.

[ruks83]

Hi,

I need to get top Entities (count) whose Cumulative%  == 70% 

Ex 1 - Topic 1

Entity Entity%  Cumulative%

Test1  36%        71%

Test2  24%        71%

Test3  21%        71%

Ex 2 - Topic 2

Entity Entity%  Cumulative%

Test1  89%        89%

EntityReport 

[Ashish_Mathur]

Hi,

Is this the result you want?  You may download my PBI file from here.

Hope this helps.

[ruks83]

It works great. Thanks Ashish. However, its not dynamic, like I had Entity% which was dynamic based on Topic selected. Is there a way to calculate top N for only selected topics?

[Ashish_Mathur]

You are welcome.  What is not dynamic in my solution?  Please elaborate.

[ruks83]

Im sorry everything looks great!!! Your solution works like a charm!!! Thanks a ton 🙂

[Ashish_Mathur]

You are welcome.  If my reply helped, please mark it as Answer.

[KellyDittmar]

@Ashish_Mathur - You're my hero! Totally solved the problem I was having for me. Thank you!! 

[Ashish_Mathur]

Thank you for your kind words.  Kinldy kudos my post and mark it as Answered.