TOP N Column Sum without Filter

[Anonymous]

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

[Ashish_Mathur]

Hi,

Your question is not clear.  Share some data and show the expected result.

[Anonymous]

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

Spoiler

Categorie	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? 

Spoiler

[Ashish_Mathur]

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.

[Anonymous]

That is not what I was looking for. I want that table with TOP N Filter but the sum in TOP N (and the percentage) should run on the Overall Sum of that column not the filtered TOP N sum. Like I said in my first post, if I have a sum of 1000 and my top 1 is 200 it has a percentage of 20%. But if i run the TOP N Filter the sum reduces to 500 and the top 1 with 200 has a percentage of 40%. That is a big difference (sure for the TOPs in correctly calculated but in overall it's not but i want the TOP Ns still weighted to the overall if it's possible, if not i will pass). 🙂 

[Ashish_Mathur · Accepted solution]

Hi,

Download my PBI file from here.

Hope this helps.

[MaheshParchure]

Good Solution

[Ashish_Mathur]

Thank you.

[Anonymous]

Thanks that looks awesome :).

[Ashish_Mathur]

Great to hear that.  You are welcome.

[TomMartens]

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

[Anonymous]

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.