cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

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

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

1 ACCEPTED SOLUTION
Super User

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
9 REPLIES 9
Super User

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

Thank you verry much!

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

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

Super User

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper IV

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

Super User

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

Helper IV

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 =
thank you

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors