Forum Discussion
TOP N Column Sum without Filter
Hello Community,
i have a question. I made a Table with Top N Values. But instead of the percetage for the Column Sum with Top N Filter i want it for the Column without the Top N Filter. Can somebody give me a hint? (but i still want the visual measure for top 30).
Example:
Value 1 = 200, Top N Column Sum 500 = 40%
Value 1 = 200, Column Sum without Top N 1000 = 20%
Thanks a lot.
Greetings Sam
11 Replies
Hey,
please excuse but I do not understand your requirements.
Please conisder to create an Excel xlsx workbook, that contains sample data, create a sheet that contains your expected result and also describe how some filtering may effect the reuslt.
Providing sample data reduces the effort to create a working solution and also avoids misunderstandings.
Regards,
Tom
Hi Tom,
since your footer says Hamburg Germany...
will ich mich einfach halten. Ich habe eine Tabelle und wenn ich den Top N Filter verwende, wird die Summe der Spalte an das TOP N angepasst. Und ich hätte gerne dennoch die volle Summe. Denn sonst ist das prozentual abweichend von dem tatsächlichen Wert.
Ist sowas möglich über das Quick Measure? Ansonsten bereite ich morgen ein Testdatei vor.
Vielen Dank.
Hi,
Your question is not clear. Share some data and show the expected result.
I don't know how to insert a .xlsx or a .pbix file? How can I do that? While I am waiting here is a DropBox Link https://www.dropbox.com/sh/1yuh15a03pw4dm0/AADwMxXa1f4sB4RX8QNE7ej2a?dl=0
SpoilerCategorie Value A1 19,96 A2 17,38 A3 52,59 A4 65,72 A5 17,29 A6 41,20 A7 95,70 A8 86,73 A9 13,23 A10 19,41 A11 22,16 A12 83,39 A13 85,56 A14 7,75 A15 8,38 A16 29,58 A17 90,72 A18 18,27 A19 20,54 A20 29,44 A21 89,84 A22 60,38 A23 33,18 A24 29,79 A25 38,76 A26 24,86 A27 35,86 A28 32,24 A29 46,11 A30 96,02 A31 90,26 A32 38,75 A33 37,83 A34 23,36 A35 4,29 A36 77,56 A37 4,47 A38 64,56 A39 42,06 A40 38,08 A41 66,30 A42 18,66 A43 39,67 A44 26,79 A45 7,06 A46 80,38 A47 66,08 A48 34,49 A49 59,31 A50 55,94 B1 83,07 B2 74,13 B3 50,87 B4 68,22 B5 39,34 B6 93,90 B7 74,82 B8 2,67 B9 24,86 B10 28,88 B11 88,48 B12 3,67 B13 84,32 B14 23,63 B15 85,35 B16 75,00 B17 19,29 B18 1,31 B19 43,00 B20 12,06 B21 83,41 B22 71,91 B23 77,91 B24 50,78 B25 4,88 B26 35,34 B27 99,90 B28 63,58 B29 73,90 B30 63,74 B31 28,90 B32 94,78 B33 15,71 B34 61,98 B35 72,08 B36 50,87 B37 88,47 B38 18,33 B39 56,83 B40 47,47 B41 53,57 B42 32,08 B43 89,91 B44 42,60 B45 96,77 B46 73,82 B47 60,38 B48 9,29 B49 98,66 B50 88,68
In this picture (spoiler 2) I made two tables with the same data. On the right one I have the TOP N filtered datas. But as you can see the percentage runs on the TOP N Sum, not the overall Sum. Is there a way how to avoid the percentage to run on the TOP N while showing data TOP N filtered?
Hi,
Images do not appear in your latest post. Anyways, i wrote these 3 measures and dragged the third one to a card visual
Total = SUM(Tabelle1[Value])
Top 30 = SUMX(TOPN(30,VALUES(Tabelle1[Categorie]),[Total]),[Total])
Top 30 % = [Top 30]/[Total]
The result is 51.77% = 2573.49/4971.31
Hope this helps.
