how can i select top records from a table having their sum equal to a value

[cristianj]

hello, 

i have to select top records , the structure is simple, id, date and value, and for each date, i have to get the first records for which the sum of value = 0.7* total value for each day something like this

Data
id	day	value
id1	01.01.2000	10
id2	01.01.2000	4
id3	01.01.2000	5				Temp
id4	01.01.2000	7
id5	01.01.2000	5					Total	Total 70%
id6	01.01.2000	8				01.01.2000	39	27.3
id7	01.02.2000	12				01.02.2000	42	29.4
id8	01.02.2000	9				01.03.2000	35	24.5
id9	01.02.2000	6
id10	01.02.2000	8
id11	01.02.2000	4
id12	01.02.2000	3
id13	01.03.2000	11
id14	01.03.2000	3
id15	01.03.2000	3
id16	01.03.2000	7
id17	01.03.2000	9
id18	01.03.2000	2
RESULT
id	day	value
id1	01.01.2000	10
id6	01.01.2000	8
id4	01.01.2000	7
id7	01.02.2000	12
id8	01.02.2000	9
id10	01.02.2000	8
id13	01.03.2000	11
id17	01.03.2000	9

Thank you

[Ashish_Mathur · Accepted solution]

You may download my revised PBI file from here.

Hope this helps.

[cristianj]

Thank you verry much!

[Ashish_Mathur]

You are welcome.

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[cristianj]

thank you very much, can you help me change it so it orders by values not by id? i made a mistake when i write the example. thank you

[Ashish_Mathur]

You are welcome.  You have completely changed the example.  Show the expected result very clearly.

[cristianj]

i've change it. thank you, it is possible to make a new table having the result structure?

[lbendlin]

You will need to specify the sort order for getting to the 70%.  The usual order for a histogram is by value descending.  See attached for an implementation example

[cristianj]

Hello, you were right , i need the sort order to be values descending, i have a small problema beacuse i don't understand how it calculate the day cumul, when o apply the example to my data, it result something like this 

and i don;t understand from where it gets the value for iphone 13 pro, pro max...

the formula is 

Val **bleep** ziln =

var mxt=max('buy_phone_details'[Buy Price €])

return calculate([Total zi],buy_phone_details[Buy Price €]>=mxt)

thank you